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

<OleDbDestination Name="stgUpdates_<#=table.Name#>"
ConnectionName="SSISIncrementalLoad_Stage">
<InputPath OutputPathName="Filter.Changed Rows" />
<ExternalTableOutput
Table="dbo.stgUpdates_<#=table.Name#>" />
</OleDbDestination>
<OleDbDestination Name="<#=table.Name#> Destination"
ConnectionName="SSISIncrementalLoad_Stage">
<InputPath OutputPathName="Correlate.NoMatch" />
<ExternalTableOutput Table="dbo.<#=table.Name#>" />
</OleDbDestination>
</Transformations>
</Dataflow>
The Biml/.NET code shown in Listing 19-24 dynamically generates an increment-
ally loading Data Flow task, given the caveats listed near the beginning of this section.
Let's complete the Biml file by creating a generic template for the final Execute SQL
task that performs the set-based update for Changed Rows between the staging table
and destination, shown in Listing 19-24 .
Listing 19-24 . The Generic Apply Staged Updates Execute SQL Task
<ExecuteSQL Name="Apply stgUpdates_<#=table.Name#>"
ConnectionName="SSISIncrementalLoad_Stage">
<# string upd ="Update Dest Set ";
foreach (var colex in table.Columns.Where(column =>
!table.Keys[0].Columns.Select(keyColumn =>
keyColumn.Column).Contains(column))) {
upd = upd + "Dest." + colex + " = Upd." + colex
+ ",";
}
var updc = upd.Substring(0,upd.Length-1) + " From "
+ table.SchemaQualifiedName +
" Dest Join [" + table.Schema.Name + "].[stgUpdates_"
+ table.Name + "] Upd On Upd." +
table.Keys[0].Columns[0].Column + " = Dest."
+ table.Keys[0].Columns[0].Column;#>