Package configurations are now the prescribed way of being able to set values within your package from an outside source. One of the options for the source is Parent Package Variable. The name is perhaps a little misleading so this article is meant to guide you through this slight confusion and into using them. It also helps to explain a key concept in SQL Server Integration Services
Setting Up Your Packages
This example is very simple. We have two packages. One is called Caller and the other is named Called. In the Caller package we have an Execute Package task which calls the other package.
The Caller Package
This package very simply has an Execute Package task. In our example we are calling a package stored in the file system. Here is how we have configured the Execute Package task. You will note again the use of Connection Managers, a very important concept in Integration Services. Here is our task.
Another thing we have to do is to configure our Parent Package Variable.
Remember in the good old days when all we had was Global Variables? Well that's all changed, now you can scope your variable to a particular container. In our package we are going to create two variables both of the same name but scoped to different containers. We have given the variables different values so we can see their effects later in the package. One of the variables is scoped to the package itself (scope = caller) and the other to the Execute Package task (scope = call the called package).
What's a container?
A container we have always thought of as a logical area of work. With this in mind then a Package is a container and a Task is also a container. It is also something that can be operated against.
The Called Package
In the called package we are going to need to declare a local variable called localVar and we are also going to have to use configurations and grab the variable value from the parent package. Here is how we configure our called, child, package to do just that. We are going to assign the variable value to the value of the local variable localVar so we can use it in our child package in a Script task
The variable name has no prefixes and is case sensitive.
Here is where the confusion initially appears. The name Parent Package Variable would suggest that the variable for which the child is looking is the variable MyExecutePackageVariable at the package level with the value of 99. This is not the case. The package is looking for the most local of variables in relation to itself with that name and in this case that is in its parent container the Execute Package task. To see this let's drop a script task onto our child package and add the following lines of code.
Public Sub Main()
Dim variableList As Variables
Dts.TaskResult = Dts.Results.Success
What this will do is allow us to see the value that is being passed down from the parent package in the variable.
As you can see from the following image, if we execute the parent package the value thrown out by the child package is that from the variable declared at the parent package's ExecutePackage task and not the package itself.
If we delete from the parent the variable declared at the parent's Execute Package scope then the value returned in the child will be from the parent package scoped variable