In this demo, I will show you how we can transfer values from one package to another package.

I will create one SSIS project. And add 2 packages in it. ParentPackage and ChildPackage as shown below.

Open the ParentPackage.dtsx file. Go to Control tab -> Right click on designer area and select “Variables”. This will open the variables window. Create variable as shown below. And assign some value in it.

 

Open the ChildPackage.dtsx file. Go to its variables window. Create one variable as shown below and dont provide any value in it. (NOTE: Even if you provide value here, it will be overwritten by parent variable’s value because we are going to do so.)

One IMP point here… Why do we need to have a child variable declared in ChildPackage in order to just read the value from ParentPacakge? Well… In one package variable can have different scope, but variable’s scope is 100 % limited to the package itself, i mean… we cant directly use one package’s variable in another package. But sure… we have such mechanism available to achive that Here we have created ChildValue variable in ChildPackage, that is the holder which will hold an incoming value.

 

Now, we will create a configuration in ChildPackage as follows:

 

Now, on clicking next, we will be asked for the Configuration Type. We will choose, to use “Parent package variable”. That means, we are interested in reading values from parent package. And provide the name of variable which you are intended to read the value of. See below screen-shot.

 

Then click next. You will see the targe property setting dialog box. Here, we will choose which property will be holding the incoming value. So, choose “Value” property of “ChildValue” variable and click next.

 

Provide some good name to this configuration and complete the wizard.

 

Now, we have done with the configuration of ChildPackage. But we also have to check whether it retrieves the value from the ParentPackage or not. So i have used ScriptTask here in ChildPackage to read the “ChildValue” variable and display it in MessageBox. See the following steps:

 

Click on “Design Script” button and it will pop-open the visual studio instance. (I have created this demo in Visual Studio 2005 so i have only VB.NET language available, But if you are developing this example in Visual Studio 2008, You will have an option of C#.NET as well… which is my all-time-fevourite language).

Write the follwoing code. Save & Close the window.

 

 

Now everything is done as far as ChildPackage is concerned. But we have to call the ChildPackage from ParentPackage. (NOTE: When you use “Parent Package Variable” configuration, values will be reflected run-time only, So you have to check it by running the package.)

We will use “Execute Package Task” in ParentPackage.dtsx and call ChildPacakge as shown below:

 

And click OK to close the dialogbox.

As you can see, we have 2 packages in 1 SSIS project. So we will provide priority – “which package will run if we debug the project”. So check the screenshot below:

 

All SET… simply press “F5” OR click on green run button () to start debugging. And you should see the following screenshot.

 

I hope this will be useful to you all somewhere.

Thanks, Khilit