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 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 discussed include key information necessary to manage your packages. Not only can these metadata be used standalone, they can also be used as part of a management framework. This chapter starts the Integration Services Framework section, where we will discuss management frameworks. Specifically, Chapter Four covers parent-child patterns, where an Integration Services package can execute another package from within its own execution.
Integration Services 2012 contains its own management framework, including logging and execution through the Integration Services service. In this and subsequent chapters, we will show how to use the available framework and enhance it to provide more information while still working around the issues we discussed.
The following are the three parent-child patterns we’ll discuss in this chapter:
- Master Package Pattern
- Dynamic Child Package Pattern
- 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 we want to do is find a way to organize how our packages execute. This organization could include parallel versus serial processing, conditional execution, and categorical batching. While some of this organization could occur in a job scheduler such as SQL Agent or Tivoli, wouldn’t it be easier if we could manage our package execution in an environment we already know?
Luckily for us, Integration Services already provides this ability! By using the workflow designer and the Execute Package Task, we can execute other packages, creating a “Parent-Child” package relationship. When we use the parent-child relationship to execute a series of packages, we call this a master package. There are two steps we need to complete in order to set up one child package for our master package:
- Assign the child package
- Configure parameter binding
Once we have created our initial package, we 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 we can modify. Let’s begin by configuring the Package menu, as shown in Figure 16-1.
Figure 16-1. Execute Package Task Editor Package Screen
This is where we set up the package that we 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, we will just use an existing package in our solution.
At this point, we could click the OK button and have a perfectly acceptable master package. Before we do that; however, we should delve into passing information between the packages using parameters in the next menu, Parameter bindings.
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! We do this through parent package parameters. This option can only be used if we are using a child package from the same project as the master package. Once we complete the setup for our package parameters, we should see the screen shown in Figure 16-2.
Figure 16-2. Execute Package Task Editor Parameter Bindings Screen
To achieve the result shown in Figure 16-2, we need to look at the Execute Package Task Editor and go to the Parameter bindings menu. Click the Add button to set up a parameter. For the Child Package Parameter, we can either select a parameter that has already been created or add our own parameter, in case we 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 to do! Next, we 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 could be used to record the package that called the child package.
If we want to test the package, we 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 we run the package, we 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 we have a working parent child package, let’s take it to the next level by creating a dynamic child package.
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 which 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. Our 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 list of the dynamic packages that we 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.
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 we will create the master package. Starting with a blank SSIS package, create a variable that is scoped to the package level. The variable should be named packageListObject and have a data type of Object. You do not need to put a value for the variable. Secondly, add a variable, also scoped to the package level, which is named packageName. This is data type String and also contains an empty value.
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 your table.
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 we just created called packageListObject. This property screen can be seen in Figure 16-5.
Figure 16-5. Execute SQL Task Editor Screen
Attach a ForEach Loop Container to the Execute SQL Task. This is where we will execute the package. Within the Collection menu 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 Screen that enumerates through each row in the packageListObject variable
Then, we need to tell Integration Services what to do with the value it retrieves when enumerating through the object list. On the Variable Mappings menu, set the variable to User::packageName and the Index to 0. This will put each value into the variable.
Finally, we’re at a point to add the part that executes the package. Similar to the creation of the master-child package, we want to use an Execute Package Task. Begin by setting the DelayValidation property to True to allow us to make the decision of what package to run at runtime. Rather than walk through the same steps as we did in the master-child package, we will go directly to the Expressions menu in the Execute Package Task Editor. This is where we set up the dynamic portion of the package. Set the Package Name Property to use the Expression @[User::packageName]. The final Expressions screen should look like Figure 16-7.
Figure 16-7. Execute Package Task Editor Expressions Screen
When the package runs, it will loop through each row in the PackageList table, set the package name 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 we store all of this information, it is important to pass important information between packages, not only from the parent to the child, but also from the child back to the parent. While this feature is not readily known, it is possible to do this using the Script Task. Let’s use our 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 our scenario, we are going to create a variable named ChildFileName of datatype String that is scoped at the package level. Attached to the Execute Package Task we created previously in this chapter, we’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, we will modify our child package. In the Script Task, add the variable User::ChildFileName to the ReadWriteVariables property list. 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 run, 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 package works 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 us to use variables and properties without having to recreate them for every object in our package. When we add a child package using the Execute Package Task, we add another layer to the parent-child hierarchy, and allow the child package to set the parent package’s variable.
Summary
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 includes 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.