The BI Monkey SSIS ETL Framework

[James Beresford]

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.

• Recoverability
• Extraction constraints
• Execution order
• Dependencies
• 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!



Get More from the original blog...