The BI Monkey SSIS ETL Framework

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!

9 thoughts on “The BI Monkey SSIS ETL Framework

  1. James, thanks for giving us your time at the AUSSUG the other week. Greatly appreciated and I think your ETL Framework is a great idea. I have developed something very similar (though more basic), but I have added a Cube over the top of the logging which I think is a very usefull addition. For instance in my cube the elapsed time measures allways go red if one of the package runs making up the number errored. So you can see how a run went (or is going) at a glance and if there was some trouble worth drilling into. With your ETL Frameworks with re-tries, I would imaging using a couple of different colours to indicate 1) Succuss, 2) Success after a retry, and 3) Failure.

    Anyhow, thanks again for your time.

  2. Hey, no problem – and yes, a cube sits in my plans – the main aim of this framework is not simply to provide control mechanisms but to provide transaparency over those mechanisms. Too often these things are built by developers who rely on their knowledge of it to debug – rather than make the effort to expose them through alien technology such as reports :)

  3. James where do I find a copy of the SSIS ETL Framework document. I have been on the codeplex link but there is only limited information on there (or what I can find) under documentation. I was hoping for an end to end document that I could read through with your best practice framework for ETL.

    Greg

  4. Hi Greg

    Documentation is a work in progress – it’s the next priority after the testing is done – which it almost is. I’m not sure if i’ll end up with a document to download, but the logical process flows are my main target to release next docco wise.

    Cheers, James

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>