Extended SSIS package executor.

The execute package task in SSIS , is lacking the ability to pass variables into the package to be executed. Not only that but the mechanism of using a For Each loop can be quite cumbersome.

This component attempts to resolve those short comings. It is designed to call a specified .dtsx package for each row in the data input. In addition, it supports multi-threading , set the maxdop to the required number of threads.

To use, simply place the component into the data flow and attach an input. Then edit the component and you will see a dialog , similar to the below.

Dialog.jpg

Use the "Get File" button to select your .dtsx file, the drop down pick list will now be populated with a list of the user variables contained with the package.
Additionally the <PackageReturnId> should be set to a unique id (integer) , this will be returned in the output along with the packages exit code , 0 = OK , <> 0 = ERROR.

The Max Dop value is the the number of threads that will be servicing the input.


IncontrolFlow.jpg


To Install

Run the installer.

Start a new bids session.
In the toolbox pane , Right click -> Choose items, on the SSIS data flow tab , you should now see "SSIS Package execute" as below. Select the tick box, and the control will now be in the toolbox.

selpkg.jpg

Last edited May 16, 2010 at 8:14 AM by daveballantyne, version 10