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

Figure 19-6 . Results of a successful execution of IncrementalLoadPackage.dtsx
By examining the results and comparing them to Figure 19-4 , we can see
SSISIncrementalLoad_Dest.dbo.tblDest has been updated to match
SSISIncrementalLoad_Source.dbo.tblSource . We can also see the up-
dated row, with ColID equal to 1 , was sent to the SSISIncrement-
alLoad_Dest.dbo.stgUpdates table.
Cool. But just wait: this is about to get awesome.
Using Biml as an SSIS Design Patterns
Engine
Let's do something really cool and interesting with Biml. Using the Increment-
alLoad.biml file as a template, and applying .NET integration—known as Bim-
lScript—found in the Biml library supplied to BISD Helper, we are going to add flex-
ibility and versatility to a new Biml file that will build an incremental load SSIS pack-
age between all the tables in a source and staging database. This is an example of the
capital “E” in ETL; this is an extraction SSIS design pattern.
Note This pattern requires that the Source and Stage tables exist prior to expanding
the Biml file to create the SSIS packages. Even with this caveat, which can be ad-
dressed, automated, and overcome, I believe this example demonstrates the power and
game-changing attributes of Biml.