Passing Variable Values from Parent Package to Child SSIS

If you find yourself creating a lot of the same tasks in multiple packages, then it may be time to place that code in a child package. By placing tasks in a child package and having multiple parent packages call that task you make it easier to update all of your packages and reduce coding. You can pass information from a parent package to a child via a Parent Package Variable Configuration.

First we will create two packages, one named parent, one named child. On both packages we will create a string variable named "strVar". In the parent package we will give the variable the value of "parent". We will pass this value to the child package. In the child package we will give the variable the value of "child", which will be over written by the parent package.

In the Parent package drag in an execute package task and point it to the location of the child package.

In the child package right click in the control flow and select Package Configurations, click add and then next. Then select Parent Package Variable in the configuration type menu. In the Parent Variable field type in "strVar", this was the name of the variable we created on the parent package, then click next.

In the left hand pane click on the plus next to the "strVar" variable and then the plus next to the properties folder under it. Then click on value so it is highlighted. This tells SSIS where to copy the value of the parent variable. Click next and finish.

To see the value of the variable drag in a script task , select the strVar in the read only variables, and enter the code: MsgBox(Dts.Variables("strVar").Value)

This will cause a popup box to appear showing the value of the variable. The value will be parent if you run the parent package. The value will be child if you run the child package alone.