To me, as a professional software developer, SSIS is severely hindered by its lack of the ability to pass variables into a package. It is such an obvious and simple piece of functionality that it seems incredible to believe that, within SSIS as standard, a parent process cannot tell a child how to behave. This is almost akin to not being able to pass parameters into a function. After trying various methods (calling DTSEXEC , storing the variables in a table, etc.), none of which I was entirely happy with, I decided to resolve the issue myself.

What I have created is a data flow component, which will call a specified .dtsx package for each input row.

For example :

We have an existing package (child.dtsx), which has the variables contained within it of ID and Name (which are of type integer and string, respectively) and we wish to call that from another package (parent.dtsx) ,populating ID and Name for each row in the data flow. How would we achieve this ?

First thing to do is to download and install the package from CodePlex (http://extendedssispkgexec.codeplex.com/). Start a NEW execution of BIDS , right click in the tool box and select ‘Choose Items’ on the ‘SSIS Data Flow Items’ tab. Select the ‘SSIS Package Execute’ item as shown below.

Drag and drop an instance of the newly created ‘SSIS Package Execute’ from the toolbox onto the data flow task. Define a data flow source, as you normally would do, and connect the output to the Package execute task. If you now edit the Package Execute Task, you will see a dialog as below.

Initially you must click the Get File button and select a package (child.dtsx in this case). You will notice that the ‘InpName’ column on the dialog has been populated with the columns from the Input and the drop down for the ‘Proc Var’ column has been populated with the variable names from the selected package. Now simply match the input columns to the procedure variables to be populated. The ‘<PackageReturnID>’ will be returned in the output along with an error code ( = 0 then Success , <> 0 then Failed). The ‘Max Dop’ field specifies the number of concurrent of threads to be executed. This ,I find, improves performance on occasions where my child package has to pull data from other host systems, which can take an indeterminate amount of time.

I am fully aware that this code is Alpha , maybe even pre-Alpha, and there is a To-Do list as long as my arm. But, for now, it would be helpful to get some feedback as to the usefulness and other functionality that could be supported. Feedback is very much wanted and appreciated.