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.

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.

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.