Parent-Child Patterns - SQL Server Integration Services Design Patterns - page 398

Child-to-Parent Variable Pattern
Parent-child patterns are an essential part of a management framework. For example,
you could use the master package pattern to group similar packages together and make
sure they are executed in the correct order. You could also use the dynamic child pack-
age pattern to run a variable number of packages. To ensure that you store all of this in-
formation, it is vital that you pass important information between packages, not only
from the parent to the child, but also from the child back to the parent. Although this
feature is not readily known, it is possible to do this using the Script task. Let's use
your existing packages to show how to pass the name of a file from the child package
to its parent.
The first step is to create a variable in the parent package. In this example scenario,
you are going to create a variable named ChildFileName of data type String that is
scoped at the package level. Attached to the Execute Package task you created previ-
ously in this chapter, you'll add a Script task. Add the ChildFileName variable as a
ReadOnly variable, and add the code in Listing 16-4 inside the Visual Basic script.
Listing 16-4 . Visual Basic Script to Display the Child File Name
Public Sub Main()
MsgBox("The name of the child file is: " & _
Dts.Variables("User::ChildFileName").Value.ToString)
Dts.TaskResult = ScriptResults.Success
End Sub
Next, modify your child package. In the Script task, add the variable
User::ChildFileName to the ReadWriteVariables property list. You will
have to manually type this in, as it will not display in the menu. Thus, your full, read-
only variables list appears as:
$Package::ParentPackageName,User::ChildFileName
Then add the line of code found in Listing 16-5 to the Visual Basic Script task.
Listing 16-5 . Visual Basic Script to Set the Child File Name Value