ETL – SSIS Step 2

The last interesting step here involves a number of calls to stored procedures that were not rewritten yet.

The original code was using a Execute SQL Task in the Control Flow to execute all of the stored procedures.

To get SSIS to multi task the sproc calls, i created a new data flow and created a data source that returned one row. This is a fast way to get the data flow moving. Then i used a MultiCast transform to split the stream into all of the independent streams. Then i used a Union All before each each stored procedure call to get it on a separate thread. This can be fine tuned once the application is in the performance test / tune stage.