Blog Post

SSIS ETL Frameworks

In my years as a BI Professional I have seen many ETL Frameworks. They range from homegrown internal solution to out-of-the-box solutions developed by small and large consulting companies and other various providers.

There are many similarities and differences between, but they are all designed to address two general requirements:

1. ETL Monitoring – At it’s most basic level this consist measuring the ETLs performance in terms  row counts (how many rows where processed?)  and duration (How long did it take?). Most ETL Monitoring Frameworks also include features that allow for notifications or alerts to be sent out when a process fails or falls outside some predefined parameters. A good framework should include not only package level but control flow and data flow monitoring as well.

2. ETL Data Management – This a very difficult to define precisely because there are some many variations and the requirements can be very specific. But generally the purpose of ETL Data Management is to control how and when data flows through the ETL solution. This can cover everything from data cleansing (Data Quality Services) to conforming/validating the data.

ETL Data Management is especially important for dimension tables. Because dimension tables are critical to good consistent analysis. Depending on your specific requirements there are many other things to consider as well (see Kimball resources at the bottom)

Now that you have few ideas about what an ETL Framework might do. Here are several resources that I have used in the past. I won’t go into detail about all there features but they all have specific issues that they address.

Build Your Own

imageObviously this would take the longest to develop but can address very complex and specific requirements. Whether or not you plan on doing it yourself I would still highly recommend feasting on Microsoft SQL Server 2008 Integration Services: Problem, Design, Solution. It will cover the details of how to build your own. Brian has built a good business out this ETL Framework.

My Metadata Driven ETL Process is something unique that has saved me a lot of headaches! Hopefully it can help you as well. Combine that with Change Data Capture and it scales very nicely.

Out-Of-The-Box

image

BIxPress is an awesome product with with too many  features to mention here and is very reasonably priced.  (and no Brian is not paying me to say that it just happens to be true)

SQL Server 2012 SSIS Framework

SQL Server 2012 is going to ship with it’s own built in ETL Framework called SSIS Catalog. If you can upgrade I highly recommend doing it!

Other Important Stuff

image

Dimension Merge SCD gives you unparalleled insight into the most important process in you ETL… The dimensional build process. And best of all it is free!

image

The Microsoft Data

Warehouse Toolkit: With SQL Server 2008 R2 and the Microsoft Business Intelligence Toolset. You absolutely have to read this book. It even includes Kimball’s version of an ETL Framework!!!

image 

BIDS Helper a great FREE BIDS plugin also has the ability to programmatically generate SSIS packages by using BiML.

It could reduce developed time and help standardize SSIS package development. Best of all it is  FREE.

These are the best resources that I have found to date. Look at your requirements and find out which combination the above works best for you.

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating