Parent Package Variable in SSIS

Parent Package Variable in SSIS

There are situations wherein you need value which is assigned to variable in one package to be used in other package.

There exist a way in SSIS to access a variable defined in one(Parent) package and utilized in other(Child) package.

Just visualize a scenario where you have a Parent Package and "n" number of child packages.Through Execute Package task you are calling all child's from Parent.

All child packages should get values from Parent package.(Values can be "Server name or DB_Name")

When we want to make use of variable values which is defined and assigned in other package(let's say Parent), the place we need to target is Child Package.

Configuration Steps as follows:

1) Go to the package(child),right click anywhere in the Control Flow Designer Area and choose Package Configuration.

2) "Package Configurations Organizer" window will pop up, click on Check-box "Enable Package Configuration" which enables "ADD" button.Click Add.

3) Package Configuration Wizard(welcome page) will launch, just Click Next. (You may or may not come across this Welcome page).

4) Now when you click Next, Select Configuration Type wizard appears.

5) From the Configuration Type drop down(defaults to XML configuration file), choose Parent Package Variable.

6) Now we have reached the most important step of this article, which is nothing but defining the Parent Variable.

7) In this place, we need to give Variable Name from which we are going to pull out values. (Parent package Variable Name).
   Note: Variable Name is case sensitive and hence you need to use the same name defined in Parent Package. We all set, to assign value to the variable in the Child package.

8) Click Next, Select Properties to Export window will appear.
9) Click on Variable Name,expand Properties and click on "Value" property.
10)Click Next, Completion wizard will summarize the configuration. Click on Finish.

Now whatever value assigned to Parent Package Variable will be assigned to Child Package during run time.

Best example, i can think of is, let's say you developed package in dev environment(with OLEDB in Parent and all Child's). Time has come to move to UAT or SIT then instead of changing OLEDB connection in each and every child package if we change in Parent Package, that will be reflected in Child Package(provided your servername and dbname are configurable through expressions with variables).