SQL Server Integration Services Design Patterns, Second Edition (2014)

Chapter 16. Parent-Child Patterns

In earlier versions of Integration Services, the data movement platform did not include a management framework, which is the implementation of the execution, logging, and deployment of the Integration Services packages. To try to fill this hole, developers created their own management framework to use in their organizations. As with any custom solution, the developers found that the management framework needed to be cared for and upgraded when new versions or new packages were introduced into the system.

Previous chapters have covered ETL instrumentation, focusing on metadata collection and validation. The metadata we discussed included key information you need to manage your packages. This chapter covers parent-child patterns, where an Integration Services package can execute another package from within its own execution. These patterns are a critical part of implementing an ETL framework.

Integration Services 2014 contains its own management framework, which includes logging and execution through the SSIS Catalog. In this and subsequent chapters, we will show you how to use the available framework and enhance it to provide more information while still working around the issues we discuss.

The following are the three parent-child patterns we’ll cover in this chapter:

· The master package pattern

· The dynamic child package pattern

· The child-to-parent variable pattern

Using these patterns, you can implement the Integration Services management functionality out of the box.

Master Package Pattern

When setting up a framework, one of the first things you want to do is find a way to organize how your packages execute. Your organization could include parallel versus serial processing, conditional execution, and categorical batching. And although you could have some of this organization occur in a job scheduler such as SQL Server Agent or Tivoli, wouldn’t it be easier if you could manage your package execution in an environment you already know?

Luckily for you, Integration Services already provides this ability! By using the workflow designer and the Execute Package task, you can execute other packages, creating a parent-child package relationship. When you use the parent-child relationship to execute a series of packages, you call this a master package. There are two steps you need to complete in order to set up one child package for your master package:

1. Assign the child package.

2. Configure the parameter binding.

Assign the Child Package

Once you have created your initial package, begin by using the Execute Package task from the SSIS Toolbox. Drag the task to the control flow, and open the task to see multiple menus that you can modify. Let’s begin by configuring the Package page, as shown in Figure 16-1.

Figure 16-1. Execute Package Task Editor Package page

This is where you set up the package that you want to execute. A new addition to the Execute Package task is the ReferenceType property, which enables developers to use the master package to run a package that is included in this project or a package that is external to the project. For this example, you will just use an existing package in your solution.

At this point, you could click the OK button and have a perfectly acceptable master package. Before you do that, however, you should delve into passing information between the packages using the parameters in the next menu, Parameter Bindings.

Configure Parameter Binding

Just calling a child package isn’t very exciting. What is exciting is tying the child package into something that the master package is doing! You do this through parent package parameters. This option can only be used if you are using a child package from the same project as the master package. Once you complete the setup for your package parameters, you should see the screen shown in Figure 16-2.

Figure 16-2. Execute Package Task Editor Parameter Bindings page

To achieve the result shown in Figure 16-2, you need to look at the Execute Package Task Editor and go to the Parameter Bindings page. Click the Add button to set up a parameter. For the Child Package Parameter, you can either select a parameter that has already been created or add your own parameter, in case you have not created the child package’s parameter yet. Keep in mind that this will not automatically create the variable in the child package. That is up to you! Next, you will assign either a parameter or variable from the master package to be stored in the child parameter. In the scenario shown in Figure 16-2, we are storing the name of the parent package in a parameter in the child package, which we could then use to record the package that called the child package.

If you want to test the package, you can create a Script task in the child package using the code shown in Listing 16-1. Make sure to put the $Package::ParentPackageName parameter in the ReadOnlyVariables property. If everything is mapped correctly, when you run the package, you should see the name of the parent package in a message box, as shown in Figure 16-3.

Listing 16-1. Visual Basic Code to Display the Parent Package Name

Public Sub Main()
MsgBox("The name of the parent package is: " & _
Dts.Variables("$Package::ParentPackageName").Value.ToString)
Dts.TaskResult = ScriptResults.Success
End Sub

Figure 16-3. Message box showing the name of the parent package

Now that you have a working parent-child package, let’s take it to the next level by creating a dynamic child package.

Dynamic Child Package Pattern

One of the nice things about Integration Services is the flexibility it provides if you want to do something a little different. For example, if you are not sure exactly which packages need to run, you can create a master package that has a dynamic child package that will only execute the desired packages. This is a great idea if you have a series of files coming in, but you’re not sure which files come in at a certain time. Your end goal is to create a package that looks like Figure 16-4. Let’s walk through an example of creating the master package and a list of the dynamic packages that you want to execute.

Figure 16-4. Completed dynamic child package pattern package

To create the table that contains the package names, run the Create and Insert statements found in Listing 16-2. Either create a database named DesignPatterns, or modify the script to run against some other database that you have available and can use for experimenting.

Listing 16-2. T-SQL Code to Create and Populate a Package List Table

USE [DesignPatterns]
GO

CREATE TABLE [dbo].[PackageList](
[ChildPackageName] [varchar](50) NULL
)
GO

INSERT INTO [dbo].[PackageList] ([ChildPackageName])
VALUES ('ChildPackage.dtsx')
GO

INSERT INTO [dbo].[PackageList] ([ChildPackageName])
VALUES ('ChildPackage2.dtsx')
GO

Now you will create the master package. Starting with a blank SSIS package, create a variable that is scoped to the package level. (In this chapter’s example, the SSIS package is named Dynamic.dtsx). The variable should be named packageListObject and have a data type of Object. You do not need to provide a value for the variable. Secondly, add a variable, also scoped to the package level, named packageName with a data type of String. Set the value of this variable to the same name as one of the packages in your project (i.e., ChildPackage.dtsx) so it can be used for design-time configuration.

Next, add an Execute SQL task in the control flow. Use the query in the Execute SQL task shown in Listing 16-3 against the database you just created for your table.(Hint: You’ll need a Connection Manager named Source that points to the DesignPatterns database).

Listing 16-3. T-SQL Code to Query the Package List Table

SELECT [ChildPackageName] FROM [dbo].[PackageList]

In addition to the SQL query, ensure the ResultSet property is set to return a Full Result Set and store it in the variable you just created called packageListObject. This property page can be seen in Figure 16-5.

Figure 16-5. Execute SQL Task Editor General page

Attach a ForEach Loop container to the Execute SQL task. This is where you will execute the package. Within the Collection page of the ForEach Loop container, set the enumerator to use Foreach ADO Enumerator, which will loop through the variable object. The ADO object source variable field should contain @[User::packageListObject]. This screen can be seen in Figure 16-6.

Figure 16-6. Foreach Loop Editor General page that enumerates through each row in the packageListObject variable

Then you need to tell Integration Services what to do with the value it retrieves when enumerating through the object list. On the Variable Mappings page, set the variable to @[User::packageName] and the Index to 0. This will put each value into the variable.

Finally, you’re at a point where you can add the part that executes the package. Similar to the creation of the master-child package, you want to use an Execute Package task. Begin by setting the DelayValidation property to True, which allows you to decide what package to run at runtime.

Rather than walk through the same steps as you did in the master-child package, go directly to the Expressions page in the Execute Package Task Editor. This is where you set up the dynamic portion of the package. Set the PackageName property to use the expression@[User::packageName]. The final Expressions page should look like Figure 16-7.

Figure 16-7. Execute Package Task Editor Expressions page

When the package runs, it will loop through each row in the PackageList table; set the PackageName property of the Execute SQL task to the current row, and execute only the packages that you need. Keep in mind that this will always run the child packages serially unless you create multiple loops and specifically code your master package to handle parallelism.

Next, we will describe how a child package can send information back to the parent package in the child-to-parent variable pattern.

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 package pattern to run a variable number of packages. To ensure that you store all of this information, 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 previously 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

Dts.Variables("User::ChildFileName").Value = "SalesFile.txt"

Once you run it, the package will finish with the figure seen in Figure 16-8.

Figure 16-8. Child-to-parent variable pattern execution

The passing of variable values from child to parent suceeds because of how containers work in Integration Services. Inside of a package, any child container, such as a Sequence container, can access its parent’s properties. Likewise, any child task, such as an Execute SQL task, can access its parent’s properties. This paradigm allows you to use variables and properties without having to re-create them for every object in your package. When you add a child package using the Execute Package task, you add another layer to the parent-child hierarchy and allow the child package to set the parent package’s variable.

Conclusion

SQL Server enthusiasts everywhere embraced Integration Services when it was first introduced as part of SQL Server 2005. The latest edition of Integration Services has been enhanced to make ETL developers even more excited than before. Integration Services 2012 added the basis for a management framework and the ability to create parent-child relationships, as this chapter discussed. We also discussed master package patterns and management frameworks.