If you've needed to close the information loop in an SSIS Parent-Child package architecture (also known as Master Package Design), Steve Fibich has a post is for you!

This is the neatest thing I've seen in a long time. Once a child package is called, the parent package variables are available for reference and update - without a single mapping operation. They're just there.

I tried the following:

Create a Parent package with a Script Task and an Execute Package Task. Create a int variable named MyParentVar and accept the default value at 0. 

Configure the script to popup the value of MyParentVar using the code MsgBox(Dts.Variables("MyParentVar").Value.ToString)

Create a Child package and add an Execute SQL Task. Set up any connection manager for the Task and enter "Select 1 as One" in the SQL Statement property. Set the resultset property to Single Row. On the resultset page set ordinal 0 to MyParentVar. In the Execute SQL Task’s properties you have to set DelayValidation to clear the red X circle.

Configure the Execute Package task in the Parent to call the Child package.

Copy the Parent package script task and paste it below the Execute (child) Package task. Wire it up so you get a popup, then the call to the child, then another popup.

Now. Execute the child. Error – there’s not a variable named MyParentVar in the child.

Execute the Parent. Popup 1 says MyParentVar = 0, which it should. Ok that, the child runs and then Popup 2 says MyParentVar = 1. The child updated it – kind of like ByRef arguments in VB.

Steve is using this to pass Object Variables containing datasets back up the pipe. The implications of this little trick are astounding! Good work Steve!

:{> Andy