I have, after at least 3 incarnations of building SSIS based ETL control frameworks, decided to do a fourth and (possibly) final one – for all to share. The alpha release of this is available now on CodePlex @ http://ssisetlframework.codeplex.com. I’ve put an aplha up, rather than a better tested Beta because the youngest BI Monkey in my household appears to be unhappy being a baby right now and is taking me away from this side project.
It’s fairly standard stuff from my point of view – it’s metadata driven framework that consists of a single control package and a template execution package. I’ve aimed to include the following features, all of which can be turned on or off in the metadata without altering any code.
- Extraction constraints
- Execution order
- Failure handling
The SSIS packages are pretty much a simple interface for where the meat of the decision making is occurring – in a bundle of stored procedures where it’s a lot easier to write and maintain complex decisions about processing than in SSIS.
I’m adhering to my own self-set principles on usability as well, namely to hit these targets:
- High visibility of operation via reports
- Fully commented code
- No acronynms in table and field names
- Any codes used exposed in a metadata table
I will be doing a demo of it at the AUSSUG session in Sydney this coming Tuesday as part of the SSIS round table which is taking place:
James Beresford: ETL Frameworks
Design and Implementation considerations when building ETL Control Frameworks, Including the debut demo of the BI Monkey ETL Framework
Kevin Wong: Practical ADO. NET
Three practical showcases using ADO.NET in SSIS including re-usable in memory resultsets
Glyn Llewellyn: Putting the T back into ETL
Looking at alternative ways in SSIS to perform data transformations and correction and discussing the advantages and disadvantages of each method
(Full details at the AUSSUG website)
I hope to see you there!