Calling Child Packages in SSIS
Use variable is calling child packages that need to pass info back to the parent package. Here's how.
- By Eric Johnson
- 07/26/2010
Calling an SSIS package from another SSIS package is, on the surface, a simple task. You just use the Execute Package Task and point it to a package on a SQL Server or somewhere in the file system (Fig. 1).
 |
| Figure 1. To call an SSIS package from another SSIS package, use the Execute Package Task. (Click image to view larger version.) |
It is pretty simple, right? But what if you need the parent package to pass information to the child package? That is accomplished by using variables in the parent package and package configurations in the child package.
Let’s look at an example. I created two packages, one named Parent and one named Child. In the parent package, I added a variable called MessageToChild (see Fig. 2).
 |
| Figure 2. Use variables to pass info to the child package from the parent. (Click image to view larger version.) |
Then I added the execute package task we looked at earlier to call the child package. Within the child package, I added a variable called Message (see Fig. 3).
 |
| Figure 3. The child package now has a variable called Message. (Click image to view larger version.) |
The next step is to get the Parent variable value into my child variable; this is where package configurations come into play. In the child package, I added a Parent Package Variable package configuration which uses the MessageoChild variable to populate the child’s Message variable.
 |
| Figure 4. Time to add a Parent Package Variable package configuration... (Click image to view larger version.) |
 |
| Figure 5. When the Parent package calls the Child package, the variable value is passed. (Click image to view larger version.) |
When the Parent package calls the Child package, the variable value is passed. Now for a quick test, I added a script to the child package with this code:
MsgBox(Dts.Variables.Item("Message").Value.ToString, MsgBoxStyle.OkOnly, "Message From Parent")
This will pop up a message box (see Fig. 6) when the parent package is run and subsequently calls the child package.
 |
| Figure 6. All done, all working. |
Any values that you want to pass have to be stored in variables in the parent package. The child package on the other hand can use those values to set any property with a compatible data type. You can set file paths, expression values, connection strings, and, as we saw, variable values.
There are other ways to get packages to “talk” to one another, like writing data to a table or file, but this is a quick and efficient way to get a parent package to send simple bits of information to child packages.
About the Author
Eric Johnson, SQL Server MVP, is the owner of Consortio Services in Colorado Springs providing IT systems management and technology
consulting. He is also the President of the Colorado Springs SQL Server
User Group. He can be contacted at www.consortioservices.com.