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>

SSIS Snack: Passing Parent StartTime to the Child Package

Introduction

Jack Corbett (Blog - @unclebiguns) tweeted recently about recording the start time of a parent package from the child package it called. I responded and ended up writing a small demo project in SSIS 2005 that you can download here. I thought I'd share it.

Build a Project with a Couple Packages

I created a new SSIS project and renamed the default package Child.dtsx. I added a new SSIS package and named it Parent.dtsx:

 

I know. Creative.

Parent.dtsx 

I need to demonstrate a difference in the start time of the parent and child packages. So I added a Connection Manager aimed at (local).master and then an Execute SQL Task with the following T-SQL statement:

waitfor delay '00:00:10'

Next, I added an Execute Package Task and aimed it at Child.dtsx. When I finished, Parent.dtsx looked like this:

 

So this package, when executed, will pause for 10 seconds and then execute the Child.dtsx package.

Child.dtsx

In Child.dtsx, I created a DateTime data type variable named ParentStartTime. I right-clicked the Control Flow and clicked Package Configurations to open the Package Configurations Organizer. I selected a Parent Package Variable Configuration Type and entered StartTime for Parent Variable Name:

 On the Select Target Property window, I selected the Value property of the ParentStartTime variable (\Package.Variables[ParentStartTime].Properties[Value]):

I clicked next and named the package configuration, then closed the Package Configuration Organizer.

By Value of By Reference? 

This package configuration will read the value of the StartTime variable in the Parent.dtsx package and pass the value into the value of the ParentStartTime variable in Child.dtsx. This is important: When variable values are passed like this - in any software language or platform - this is called By Value (or ByVal). It means the value from the source variable is read into the value of the destination variable. So if you change the value of the Destination variable the Source variable value is unaffected.

The other way to pass variable values is called By Reference (or ByRef). It means a pointer to the Source variable value is passed into the Destination variable. So if you change the value of the Destination variable, you're actually changing the value of the Source variable - because of the pointer.

You can pass variables between Parent and Child packages in SSIS ByVal and ByRef using the Parent-Child design pattern. I covered both in my PASS Summit 2009 presentation Applied SSIS Design Patterns.

Back to Child.dtsx...

Finally, I added a Script Task to the Child package Control Flow. I named it Display Parent and Child StartTimes; set the ReadOnlyVariables property to ParentStartTime,StartTime; and added the following code to the Script Editor:

Conclusion

Executing the Parent.dtsx package yeilds the following message box:

The parent package StartTime variable is passed into the Child package's ParentStartTime variable. I display both the start time of Parent.dtsx and Child.dtsx to demonstrate they are, in fact, different - thanks to the 10-second delay contained in the Parent package's Execute SQL Task. 

:{> Andy

Published Monday, January 25, 2010 8:00 AM by andyleonard

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

 

GK said:

Thanks for the post Andy !!

Any chane you could post something on childe to parent.

Cheers

May 14, 2010 9:17 AM
 

Kranthi said:

Hi.. Is it possible to change the value of parent variable from the child package??

September 13, 2010 1:45 AM
 

andyleonard said:

Hi Kranthi,

  Yes, but it's not well-documented. The short answer: parent variables are available to the child package while running in the parent-child pattern. And, they are available ByRef, which means you can change the value of the variable from inside the child, and the new value is reflected in the parent.

  I discuss it in an article I wrote for SQL Server Standard magazine (you need to register for PASS to access but registration is free and relatively painless): SQL Server Integration Services Package Communication at Runtime (http://www.sqlpass.org/LinkClick.aspx?fileticket=-u_hxRAQSuk%3d&tabid=236&mid=1477).

:{> Andy

September 13, 2010 8:01 AM
 

Luis Figueroa said:

Very straight to the point explanation. Thanks!

December 15, 2010 2:24 PM

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