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
Although this is a very old post, I would like to share my understanding on this statement – "The child updated it – kind of like ByRef arguments in VB."
To me it seems more akin to scope-declaration. If you declare a variable with the same name as in the parent (in this case MyParentVar), the Child package will find this local variable. If you do not declare it, then it goes to the next level up – which is the parent package. You can also think of it as a "global variable" within the universe of the package.
For ByRef, the arguments still need to be explicitly passed between the caller and the called methods, where as with the scoping, the access is implicitly available.
Hi Anoop,
You are correct, and your explanation closely resembles my current understanding of the process. Thank you for your feedback.
:{>
This is one big difference between Execute Task and Execute process invoking dtexec, loads of guys think they are the almost same, but the true is the master and child package are sharing the same stack, kind of. It would be great to have a technical doc from Microsoft about the implementation of Master/Child package.