Pass SSIS Variable from Parent to Child Package

By: Bhavesh Patel | Updated: 2019-07-03 | Comments (4) | Related: More > Integration Services Development

Problem

When working with SQL Server Integration Services (SSIS), we might need a package that calls another package by executing a package task. However, there may also be a need to pass a variable value from the Parent package to the Child package. This article will cover how this can be done with examples to demonstrate the steps.

Solution

As part of SQL Server Integration Services (SSIS) development, we often need to create user-defined variables in packages. There are two types of variables supported in SSIS.

System variables
User defined variables

There are several system variables available in SSIS. We can’t create additional system variables, but we can create as many users defined variables as the package requires. Both of these variable types store data and the values can be accessed during package execution. During execution, in order to pass package information to other objects, we can store data in user-defined variables. We can easily bind parameters to variables and evaluate expressions also. We can easily handle iterative (foreach loop or foreach loop container) SSIS tasks with the help of variables. In addition, we can map the value of the variables between SSIS packages.

Business Scenario

In the SSIS project, I have two packages; parent and child package. In order to execute the child package from the parent package and pass values from the parent to the child, I need to map values between these packages using variables.

As per the use-case, during package execution, we need to map the value of the variables between a parent and child package. There are two basic ways to map variable values between SSIS packages.

In the Project Deployment model, we can use a script task for mapping values of a variables between SSIS packages.
In the Package Deployment model, we can use the Package Configuration Wizard to configure a package variable.

SSIS Pass Variable from Parent to Child Package Using a Script Task

In this demonstration, I will create a variable in the parent package, assign a value and use this variable in the child package using a Script Task. For the purpose of demonstration, the child package will just display the value in a Message Box to show that it worked.

I open Visual Studio and create a new SSIS (SQL Server Integration Services) project called "Variable_Mapping_Using_Script_Task".

Created a SSIS project Variable_Mapping_Using_Script_Task.

My initial project looks like below.

Right click on the Package.dtsx and rename to Parent.dtsx.

Right click on SSIS Packages and select New SSIS Package.

Right click on Package1.dtsx and rename Child.dtsx.

Configure Variables in SSIS Packages

I now have two packages, Child.dtsx and Parent.dtsx. I am going to add a variable in the parent package.

Right click in the Control Flow and select Variables.

In the variables Property window, I am going to add variable "passingvalue". I select the Data Type as Int32 and typed "555" in the Value column.

I need to put the variable in child package too. So again, right click in the Control Flow and select Variables and add variable "gettingvalue" in the child package and keep the default value of 0, but make sure the Data Type is Int32.

I have added variable in child package and keep value as default 0

Configure Execute Package Task

To execute the child package from the parent package, I am going to add an Execute Package Task in the parent package.

The Execute Package Task allows calling other packages which are present in SQL Server, File System and packages present in the same project. In the parent package, I dragged the Execute Package Task from the SSIS Toolbox to the Control Flow.

I have taken a execute package task in parent package.

Now, right click on the Execute Package Task and go to the Execute Package Task Editor as shown below. I need to configure the package properties here.

There are two reference types available in the execute package task; Project Reference and External Reference. When calling a package that is located in SQL Server or the File System select External Reference. Since both packages are available in a single project, I will select Project Reference as the ReferenceType and in the PackageNameFromProjectReference I enter Child.dtsx.

Configure Script Task for Mapping and Displaying Variable Value

In the child package, for the purpose of mapping the value of the variables, I dragged a Script Task from the SSIS Toolbox to the Control Flow.

Right click on the Script Task and open the Script Task Editor. For the ReadOnlyVariables I use Passingvalue and for ReadWriteVariables I use gettingvalue. I kept the other parameters with the default values.

I have chosen variable;
Passingvalue is keep in Readonlyvariable. And I keep gettingvalue to write only variable. Because I am going to write the value in variable gettingvalue.

I then click on the Edit Script button and enter the C# code as shown below that maps the values and displays a message box for demonstration purposes. As a result of package execution, I am expecting to get value 555 in the message box which was assigned to the parent package variable passingvalue.

In C#, I wrote the script to mapping variable value. in second line i have added code for display value in messagebox.

Before executing, I marked the parent package as Set as Startup Object, so when this is run the parent package executes first.

I have marked as startup Object to parent package.

When I execute the package, the package is executed successfully and I get value 555 in the message box, which was passed from the parent package. This is just a simple example, but now that the child package has this value it can be used anywhere in the child package.

SSIS Pass Variable from Parent to Child Package Using Package Configuration Wizard and Package Variable

In this demonstration, to map variables from the parent to the child package, I will use package configuration which is available in the package deployment model only and I will use the Script Task in the Child package to display a message box to show the value.

First, I created a project "Variable_Mapping_package_configuration" then created SSIS packages for the Parent and Child. See the other example above on how this was done.

This screenshot for the displaing , my both package is successfully, If you need steps then follows demonstration1 same.

Convert to Package Deployment Model from Project Deployment Model

Package configuration is not supported in the Project Deployment model, so I am going to change to the Package Deployment model.

In the Solution Explorer, right click on the solution and select Convert to Package Deployment Model.

I am going to convert a deployement model, from project to package...

After doing this, it will check the package for compatibility. As we can see below all items passed.

For converting in package,All things has been passed which is inherited from project itself..

Configure Variables in Packages

Now the project is running under a legacy Package Deployment Model. I added variable Passingvalue to the Parent package and variable gettingvalue to the Child package respectively. These are same steps that I did in the example above.

Configure Package Configuration to Map Variables Between Packages

Right click on the Child package Control Flow and select Package Configuration Wizard and enable the property "Enable package configurations".

After enabling package configuration, I am going to add a configuration type as Parent package variable and assign the value parent variable as Passingvalue.

For the purpose of variable mapping,here need to typed parent variable name correctly

After entering the parent variable, I select next and then select the property Valuetomap this value from passingvalue to gettingvalue.

Here, value of the variables mapped also.

Press next and assign a Configuration name. After this is all done, the Package Configuration Organizer will look like below.

This is the screen which is displaying the mapping variable name.

Configure Execute Package Task in Parent Package

In the parent package, I dragged an Execute Package Task from the SSIS Toolbox.

Initiated execute package task from toolbox.

Now we need to configure the task. Since this is the Package Deployment Model, I need to select the reference type as External Reference. I will get the package from a project file so keep the location as File system.

To get the child package connection, I performed these steps.

Go to the Connection Window and browse the files from the project location and select the Child package.

This is the legacy package deployment model. so i referred as a reference external references.

Configure Script Task to Display Variable Value

In order to display the variable value, I am going to add a Script Task in the Child package.

Now I need to just display the value of the variable so I keep ReadOnlyvariables as gettingvalue. The other options are the defaults.

Already variable is mapped also so for the display the variable value, I need to take variable gettingvalue as a readonlyvariables

To add the code to display the value in a message box, click on Edit Script and enter the code below.

I have added code for display the variable value

Before execution, I marked the parent package to Set as StartUp Object by right clicking on the parent package in Solution Explorer and setting the option.

I have marked to parent package to satrtup package.

Package Execution

Now I am going to execute the project and we can see the message box with the value.

Next Steps

Kindly run on test servers before rolling out to production.
SQL Server Integration Services (SSIS) Tutorial.
Check out Passing variables between packages of the same project.
Check out variables in integration services.
Get the SSIS project Variable Mapping using script task.
Get the SSIS project Variable Mapping using package configuration.

About the author

Bhavesh Patel is a SQL Server database professional with 10+ years of experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2019-07-03

Pass SSIS Variable from Parent to Child Package in SQL Server Integration Services