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
Obviously 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
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
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!
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!!!
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.
