Calling Child Packages in SSIS

Use variable is calling child packages that need to pass info back to the parent package. Here's how.

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

Execute Package Task
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).

Adding MessageToChild variable
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).

Adding Message variable
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.

add a Parent Package Variable package configuration
Figure 4. Time to add a Parent Package Variable package configuration... (Click image to view larger version.)

 

Parent package calls the Child package
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.

Sweet child package o' mine
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.

Featured

  • Microsoft Warns IT Pros on Windows Netlogon Fix Coming Next Month

    Microsoft on Thursday issued a reminder to organizations to ensure that their systems are properly patched for a "Critical"-rated Windows Netlogon vulnerability before next month's "update Tuesday" patch distribution arrives.

  • Microsoft Nudging Skype for Business Users to Teams

    Microsoft on Thursday announced some perks and prods for Skype for Business unified communications users, with the aim of moving them to the Microsoft Teams collaboration service instead.

  • How To Improve Windows 10's Sound and Video Quality

    Windows 10 comes with built-in tools that can help users get the most out of their sound and video hardware.

  • Microsoft Offers More 'Solorigate' Advice Using Microsoft 365 Defender Tools

    Microsoft issued yet another article with advice on how to use its Microsoft 365 Defender suite of tools to protect against "Solorigate" advanced persistent threat types of attacks in a Thursday announcement.

comments powered by Disqus