Biml and SSIS Frameworks - SQL Server Integration Services Design Patterns - page 510

ADO.Net connection to the SSISConfig database. Set the SQLStatement prop-
erty to the following T-SQL script:
Select PackageFolder + PackageName
From cfg.Packages
Where PackageName = 'Child1.dtsx'
On the Result Set page, add a resultset. Set the Result Name to 0 and the Variable
Name to User::ChildPackagePath . Connect a precedence constraint between
the Get Package Metadata Execute SQL task and the Execute Child Package Execute
Package task. Execute the Parent.dtsx package to test it. What happens? The Get
Package Metadata Execute SQL task runs a query that returns the full path to the
Child1.dtsx package stored in the SSISConfig.cfg.Packages table. The re-
turned path is sent into the ChildPackagePath variable. Remember, this variable
controls the Child.dtsx file connection manager, which is used by the Execute
Package task.
Alter the query in the Get Package Metadata Execute SQL task to return
Child2.dtsx and retest.
Introducing SSIS Applications
An SSIS application is a collection of SSIS packages that execute in a prescribed order.
Let's start by adding a couple of tables and supporting stored procedures to the
SSISConfig database.
First, create a table named cfg.Applications , and a stored procedure to add
them applications to the table, in SSISConfig using the T-SQL in Listing A-6 .
Listing A-6 . Building cfg.Applications and cfg.AddSSISApplication
/* cfg.Applications table */
If Not Exists(Select s.name + '.' + t.name
From sys.tables t
Join sys.schemas s
On s.schema_id = t.schema_id
Where s.name = 'cfg'
And t.name = 'Applications')
begin