If you?ve ever developed any kind of SSIS auditing framework, chances are you?ve wanted to pass variables from the child package to the parent package. As you know it can be accomplished, but it takes some hoop jumping including writing some code in a script task. As I?ve said before, scripting is not my strong suit, so if there is a way to work around it, I?ll give it a try.
Thankfully Pragmatic Works has made the work around unnecessary with the new component in the ever growing list of SSIS components. The Advanced Execute Package Task component allows you to easily configure the passing of variables between packages in both directions.
The component is easy to configure. Once you?ve selected the location of the package you want to execute, either SQL Server of File System, the next step is to assign the direction of the variable mapping. There are three options, read from child package, write to child package, read and write variable from child package.
Once that is selected (in this case I?m going to read the variable from the child package) the next step is to select the variable from the child package you want to read. The nice thing that the Advance Execute Package Task does, is that it only allows you to map variables of the same type. This means that if you choose a string variable in the child package you can only choose string variables in the parent package.
Once the variable mapping is set the way you want it, click the add Mapping button. This adds the mapping to the package and you can now configure another variable.
With this component it is very easy to pass variables back and forth between packages. Don?t take my word for it, download Task Factory for yourself and try it out.