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 ExecutePackage task which calls the other package.

The Caller Package

This package very simply has an ExecutePackage task. In our example we are calling a package stored in the filesystem. Here is how we have configured the ExecutePackage 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.

Variables

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 ExecutePackage 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 ExecutePackage 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 vs As Variables

        Dts.VariableDispenser.LockOneForRead("localVar", vs)
        MsgBox(vs("localVar").Value.ToString())

        vs.Unlock()

        Dts.TaskResult = Dts.Results.Success
End Sub

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 ExecutePackage scope then the value returned in the child will be from the parent package scoped variable

This article was written for SQL Server 2005 Beta 2 (Yukon), and may be updated to reflect changes in future releases of this product.