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, as shown below.
It is pretty simply 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 have created two packages, one named Parent and one named Child. In the parent package I have added a variable call MessageToChild as shown.
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 as shown below.
Okay, 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.
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 the following code:
MsgBox(Dts.Variables.Item("Message").Value.ToString, MsgBoxStyle.OkOnly, "Message From Parent")
This will pop the following message box when the parent package is run and subsequently calls the child package.
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. Obviously 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.