Assigning SSIS Parent Package Variables to Child Packages in Project Deployment Mode

It’s very common in an ETL Process to pass variable values form parent package to child package. e.g Parent Package may generate Extract Window Begin, End Dates, ETL Execution ID etc. and pass it down to all child packages.

SSIS 2008 and earlier provided Package Configurations to set child package variables from parent package. In SSIS 2012 and later if you are using project deployment mode, you can’t use Package Configuration any more. The following are two ways of passing variables from parent package to child package.   

Execute Package Task Parameter Bindings.

Define a package level parameter in Child Package.

Use Execute Package Task Parameter Bindings in Master Package to bind master package variable to child package parameter.

The parameter in child package can be used directly or assigned to a child package variable and used. 

Using Script Task

The parent package variables are available to child package at run time only (not at design time). To use this behavior –

1. Define a variable in child package.

e.g. if Parent package variable is startDate. The child package variable can be cStartDate.

2. Create a script task in Child Package.

3. Configure parent package variable as read only in script task.

  

4. Inside the script task assign the parent package variable value to child package variable.

Dts.Variables["cStartDate"].Value = Dts.Variables["startDate"].Value;

The child package variable is now set with parent package value.

The disadvantage with this method is that child package cannot be run standalone (for trouble shooting, unit testing etc). The script task would have to be disabled to make the child package run when not called from parent.