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