THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Andy Leonard

Andy Leonard is CSO of Linchpin People and SQLPeople, an SSIS Trainer, Consultant, and developer; a Business Intelligence Markup Language (Biml) developer; SQL Server database and data warehouse developer, community mentor, engineer, and farmer. He is a co-author of SQL Server 2012 Integration Services Design Patterns. His background includes web application architecture and development, VB, and ASP. Andy loves the SQL Server Community!
Note: Comments are moderated. Spam shall not pass! </GandalfVoice>

Accessing Parent Package Variables In Child Packages... Without Configurations

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

Published Sunday, September 30, 2007 7:57 PM by andyleonard
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Anoop Verma said:

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.

January 31, 2013 6:44 PM
 

andyleonard said:

Hi Anoop,

  You are correct, and your explanation closely resembles my current understanding of the process. Thank you for your feedback.

:{>

January 31, 2013 8:38 PM
 

mario amatucci said:

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.

January 10, 2014 11:44 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

My Company


Other Blog

Check out my personal blog...
http://andyleonard.me

Contact Me

Twitter: @AndyLeonard
Email: andy.leonard@gmail.com

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement