ETL generation in SSIS (adult steps)

Full generation of ETL is the missing component towards the model driven design of the Data Warehouse and I am still pursuing various methods for various ETL suites to add to this concept. Some time ago I looked into ETL generation for Microsoft SSIS using the available DTS libraries (see the post) which really were baby steps. At some point one of my colleagues suggested looking into ‘BIML’ (Business Intelligence Markup Language) and the accompanying compiler. It turns out that this really is a powerful method for generating SSIS packages: after playing around for a couple of hours I basically completed one of the ETL templates for generation similar to the one I used in the OWB demonstrations.

The BIML definition of a package can be extended using custom .Net code (C# is most commonly used) to generate packages for multiple tables. The BIML file itself is then compiled by the BIML Package Generator which is part of the BIDShelper plugin for the Business Intelligence Developer Suite (Visual Studio). After downloading the BIDShelper plugin from codeplex you’re basically up and running almost instantly. More information is found here.

At this stage I am working on updating my work-in-progress .Net front-end with the BIML package generator so I can use this common interface to manage metadata and drive the generation of the SSIS packages. My local installation of the BIML / BIDShelper that sources metadata from the SQL Server data dictionary works perfectly fine and has already proven very valuable.

Over the next few weeks I will translate the ETL steps for Data Vault into BIML to fully utilise the many deterministic patterns that Data Vault provides.