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

Click the OK button to close the Add Variable window and change the Data Type
of the ApplicationName variable to String. Change the Parameter Name to Ap-
plicationName . Navigate to the Result Set page and change the 0 result name vari-
able from User::ChildPackagePath to a new variable with the following set-
tings:
• Container: Parent
• Name: Packages
• Namespace: User
• Value Type: Object
Click the OK button to close the Add Variable window, and click the OK button to
close the Execute SQL Task Editor. Delete the precedence constraint between the Get
Package Metadata Execute SQL task and the Execute Child Package Execute Package
task. Drag a Foreach Loop container onto the control flow and then drag the Execute
Child Package Execute Package task inside it. Add a precedence constraint from the
Get Package Metadata Execute SQL task to the new Foreach Loop container, and re-
name the Foreach Loop container Foreach Child Package . Open the Foreach Child
Package Foreach Loop container's editor and navigate to the Collection page. Change
the Enumerator to Foreach ADO Enumerator. In the ADO Object Source Variable
drop-down, select the User::Packages variable. Accept the default Enumeration
Mode: Rows in the First Table.
Navigate to the Variable Mappings page in the Foreach Loop Editor. Click on the
Variable drop-down and select the User::ChildPackagePath variable. The In-
dex property will default to 0—do not change it.
The changes we just made accomplish the following:
1. Execute the cfg.GetSSISApplications stored procedure in
the SSISConfig database, passing it the value contained in the Ap-
plicationName variable.
2. Push the full result set returned by the stored procedure execution into
an SSIS object variable named Packages .
3. Configure a Foreach Loop to point at each row stored in the Pack-
ages variable in the order returned.