Implementing Parent package configuration in SQL Server Integration Services

Scenario - Parent package configuration is very useful when it is required to pass the value from the parent package to it's child package. The main advantage to using this configuration is to avoid the calculation of the variable again and again in every package, we can get the value of such variable from the parent package. For example the main database connection string, Any global variable or Database name and so on.

Usage of SQL Server configuration
Parent package configuration is more useful whenever it is required to access any variable from the child package.

Implementing the Parent package configuration -

Let's create a simple and short implementation for Parent package configuration. To verify this configuration let's pass the value of a variable from the Parent to it's Child package.

Step 1 - Open SQL server Business Intelligence Development Studio and create a new Integration Services Project -

Step 2 - Let's create two packages,  parent package as well as child package -

Step 3 - Open the Parent package and create a variable called Var_Global_DB and assign the value 'Global Database Name'. Later we will fetch this variable from the child package -

Step 4 - Drag an Execute Package Task from the tool box and double click on it. A wizard for Execute Package Task Editor will appear.

Step 5 - Select the Package tab from the left side bar and choose File system as Location in the right side bar.

Step 6 - Create a new connection for File system location by selecting the New connection from the connection drop down list.

Step 7 - Provide the path of the child package with the help of browse button and Click twice on OK  button -

Now the parent package can call the Child package through the Execute Package Task. 

Step 8 - In the child package create a variable called Var_Child. It will hold the value of the parent package variable.

Step 9 - Open the Child package and select Package configuration by right clicking on the package.

Check on Enable package configuration and click on Add button -

Step 10 - Select Parent package variable from the Configuration type dropdown list and specify the variable name which we have already declared at the Step -3 then click on Next button.

Step 11 - Expand the variable properties from Var_Child and select it's Value then click on Next button.

Step 12 -  Provide the Configuration name and click on Finish button.

Also close the main Package configuration wizard. Parent package configuration has been completed here.

Step 13 - To Verify the Parent package configuration, let's check the value of the variable Var_Child in the child package which would be holding the value from the parent package variable using Parent package configuration.

Step 14 - Drag and drop a script task from the toolbox into child package and double click on it. Pass Var_Child as  ReadOnlyVariables then click on Edit Script button.

Step 15 - Write the below line of code into the script task -

public void Main()

        {

            // TODO: Add your code here

            Dts.TaskResult = (int)ScriptResults.Success;            MessageBox.Show(Dts.Variables["User::Var_Child"].Value.ToString());

        }

Close the script task editor and click on OK button into the script task.

Step 16 -  Now we are done with all the changes. Let's run the parent package, It should populate the message box from the child package with the variables's value which has initialized in the parent package -

We are done here with the parent package configuration. I believe this article will help you to understand on Parent package configuration is SQL Server Integration Services.

Please provide your comments if this post is useful for you,