Getting Started with Adobe After Effects - Part 6: Motion Blur

Upload Image Close it
Select File

Browse by Tags · View All
SQL Server 4
Performance 4
#DBA 3
Tips and Tricks 2
Syndicate 2

Archive · View All
July 2010 3
June 2010 3
May 2010 3
August 2010 2
September 2010 1

dave ballantyne's Blog

SSIS Package Reuse

May 19 2010 11:43AM by dave ballantyne   

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 ( 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.

Tags: BI, SSIS, MSBI, BRH, Syndicate, #BI,

dave ballantyne
110 · 1% · 466



  • Child packages can read variables from parent packages. Using a proper structure, variable values can be changed for each record in loops before giving a call to child packages.

    I feel that the same can be achieved by executing a batch of dtexec commands, and parameters can be specified as the command line parameter values.

    By using this task, I am interested to learn the benefits of executing packages using this component, compared to executing it from this dtexec or execute package task.

    commented on May 19 2010 9:27AM
    168 · 1% · 293
  • I do agree with the subject at hand. This sounds interesting, but I would be a tad bit concerned about calling a child dtsx package for each row and how that would be handled internally. My question would be - is it better to pass all or a batch or rows at a time to the child package, perform it's operations and then pass them back vs calling it for each row? I supose it depends on what you are doing, but I often times deal with billions of rows a time.

    I know MS is releasing bascially report parts with RS R2 2008. It would be nice to see something similar for SSIS, where I could write a process/function once and reference it within multiple packages.

    commented on May 25 2010 1:15PM
    Brian Filppu
    2754 · 0% · 4
  • How to access System variables like ErrorCode, ErrorDescription of parent package into script of child package ?? Please provide solution...

    commented on Jan 11 2013 6:51AM
    2834 · 0% · 3

Your Comment

Sign Up or Login to post a comment.

    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]