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.
