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

This Biml code, like much in this Biml file, is copied from the Increment-
alLoad.biml file and modified to accept .NET overrides from the ForEach loop.
Each SSIS package generated when this Biml is expanded will be named consistently:
IncrementalLoad_< Source Table Name > .
Also note the ConstraintMode attribute of the Package node is set to "Lin-
ear" . In the IncrementalLoad.biml file, this was set to "Parallel" . The
differences are subtle but powerful. First, the Biml compiler will automatically create
precedence constraints for you. Specifically, it will create an OnSuccess precedence
constraint in the control flow from one task to the next, based on the order they appear
in the Biml file. This functionality makes scripting and simple file authoring extremely
quick. Second, you can eliminate InputPath nodes in the Data Flow task because the
InputPath will connect to the default output path of the transform that appears directly
before it.
Immediately following the <Package> tag, add a Tasks node, followed by an Ex-
ecuteSQL node configured as shown in Listing 19-22 .
Listing 19-22 . Adding Tasks and the Truncate Staging Table Execute SQL Task
<Tasks>
<ExecuteSQL Name="Truncate
stgUpdates_<#=table.Name#>"
ConnectionName="SSISIncrementalLoad_Stage">
<DirectInput>Truncate Table
stgUpdates_<#=table.Name#></DirectInput>
</ExecuteSQL>
Again, note the generic naming of the Execute SQL task that performs the truncate
operation on the staging table. The name of the Source table will replace the
<#=table.Name#> placeholder when the Biml file is expanded. It will be named
differently for each table in the Source database, but it will also be descriptive and ac-
curate.
In the next listing ( Listing 19-23 ) , I am simply going to show you the Biml for the
incrementally loading Data Flow task. Each component includes .NET code where ne-
cessary to make the Biml generic enough to respond to different Source table schemas.
Listing 19-23 . The Generic Data Flow Task