Hi, I have a package which will call two dataflows in a for each loop. The first dataflow will build a stage table joining data from source. the second will process all rows in the stage table loaded in the first dataflow. The SQL in each is simple and fast to run. (EG: the second dataflow is simply select * from stage).
On any given run the For Each Loop will loop 600- 800 times.
The problem I am having is that each time either of the dataflows runs in the loop there seems to be a 4-6 second delay before it reads data. Even in the second one where it is simply a select *. Combined this adds to about 8 seconds of delay (Validation maybe?) per loop instance. Therefore, I get only 3-4 loop instances run per minute. So my job can take 3 hours to run.
Is there any way I can eliminate this delay on reading in the Dataflows to cut my runtime to a fraction of what it is now?