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

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 pack-
ageListObject 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]