Business Intelligence Markup Language - SQL Server Integration Services Design Patterns - page 479

"Data Source=(local);Initial
Catalog=SSISIncrementalLoad_Source;Provider=
SQLNCLI11.1;Integrated Security=SSPI;" />
<Connection Name="SSISIncrementalLoad_Stage"
ConnectionString=
"Data Source=(local);Initial
Catalog=SSISIncrementalLoad_Stage;Provider=
SQLNCLI11.1;OLE DB Services=1;Integrated
Security=SSPI;" />
</Connections>
As in the IncrementalLoad.biml file we designed in the last section, the
Connection nodes are the templates for SSIS Connection Managers in the SSIS pack-
age. Next, add a Package node immediately after the </Connections> tag. Here we
will make a crucial modification to this Biml file and its capability. We begin a C# loop
here that spans all but the last two lines of this Biml file. Your Biml file should now in-
clude the code from Listing 19-20 , immediately after the </Connections> tag.
Listing 19-20 . Adding the Packages Node and Starting a Loop
<Packages>
<# foreach (var table in tables) { #>
The loop defined in Listing 19-20 will drive the Biml engine as it creates an SSIS
package for each table found in the SSISIncrementalLoad_Source database.
Because we are using the SSIS incremental load design pattern as the template for this
package, this Biml file will construct an incremental load SSIS package for each of
these tables.
The variables defined above are used later in the Biml file. Immediately after these
variable declarations, add the Package node shown in Listing 19-21 .
Listing 19-21 . The Package Node with .NET Replacements
<Package Name="IncrementalLoad_<#=table.Name#>"
ConstraintMode=
"Linear"
ProtectionLevel="EncryptSensitiveWithUserKey">