Can I do this?

I have a mysql connection setup as Read and a SQL Server setup as Write.
I have changed the timeout on both to 0.

By using SQL command and at start, I run a mysql stored procedure, via call xxxxxx. That process takes about 40 minutes to run. I am not sure it's finishing but it might be.

I also have another SQL command with Start that runs a truncate table on the SQL Server using the Write option. I believe that works.
 
Then the normal per record process should run which should copy a table that's in mysql to sql server. This table should have been loaded in the first start command above.

Then I have a SQL command with Finished that runs an update statement on sql server (write). 

I am expecting the mysql stored procedure to run, and the sql server truncate (don't care which one runs first), then the normal processing which copies records to sql server,and then the final process which should update some records in sql server using the data that was copied from mysql to sql server.

Should that work??

If so, Is there a clean way to trap errors, etc, and make sure all 4 steps work properly?

Thanks for any help anyone is willing to give.

Alan
 

Hi Alan,

In my opinion it should work within one processing step. I don’t know how many records are in your table you want to delete/truncate. In case this is as small amount e.g. less than 100.000 you can use also “DELETE FROM” instead of “TRUNCATE TABLE”. In this case all steps are covered into a big database transaction. This means in case of an error everything would be rollback automatically.

The common processing order is:

1. Execute the defined START functions (Heater)
   
   
2. Initialize the READ Side, run also defined connection based SQL Statements into the Adapter
   
   
3. Initialize the WRITE Side, run also defined connection based SQL Statements into the Adapter
   
   
4. If configured, empty the table, using delete or truncate depends on you setting
   
   
5. Run Each Row defined functions (Heater)
   
   
6. Execute the defined END functions (Heater)
   

To monitor whether your definition works properly you can use the RETURNCODE from the Batch Module and the provided output logging mechanisms. More details can be found here: The FlowHeater Batch Module

A much better way is to use the FlowHeater Server to execute your definition. Here you can send emails notifications in case of errors/warnings including the current output.