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


By:   |   Updated: 2019-07-03   |   Comments (1)   |   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.

  1. In the Project Deployment model, we can use a script task for mapping values of a variables between SSIS packages.
  2. 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.

First, I opened SSIS project

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

I renamed SSIS project

Right click on SSIS Packages and select New SSIS Package.

I am going to add new package in list

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

Both packages added successfully.

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.

Added variables in packages

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 have assigned the value of variable

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.

execute package task editor

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.

I took script task from SSIS toolbox.

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.

control flow

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".

Package configuration wizard

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.

Configure Script Task

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.

control flow
Next Steps


Last Updated: 2019-07-03


get scripts

next tip button



About the author
MSSQLTips author Bhavesh Patel Bhavesh Patel is a SQL Server database professional with 10+ years of experience.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Sunday, December 08, 2019 - 12:01:09 PM - Samuel Back To Top

Is it possible to do the other way arround? From Child to Parent



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools