Pass SSIS Variable from Parent to Child Package in SQL Server Integration Services
By: Bhavesh Patel | Updated: 2019-07-03 | Comments (1) | Related: More > Integration Services Development
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.
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.
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".
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.
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.
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 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.
Before executing, I marked the parent package as Set as Startup Object, so when this is run the parent package executes first.
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.
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.
After doing this, it will check the package for compatibility. As we can see below all items passed.
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.
After entering the parent variable, I select next and then select the property Valuetomap this value from passingvalue to gettingvalue.
Press next and assign a Configuration name. After this is all done, the Package Configuration Organizer will look like below.
Configure Execute Package Task in Parent Package
In the parent package, I dragged an Execute Package Task from the SSIS 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.
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.
To add the code to display the value in a message box, click on Edit Script and enter the code below.
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.
Now I am going to execute the project and we can see the message box with the value.
- 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.
Last Updated: 2019-07-03
About the author
View all my tips