SSIS Event Log Business Intelligence

Last Thursday I presented at the San Diego SQL Server User Group (www.sdsqlug.org) a codeplex project I developed based on the SSIS Event Logs. Below are links to the codeplex project as well as the slides I presented.

The inspiration for this project comes from a former position I held managing a team of database developers in which we were responsible for all the Customer Data Integration (CDI) processes as well as the Extract Transform and Load (ETL) processes for our Enterprise Data Warehouse (EDW).

While in that role I found that some of the CDI proceses were running slow to the point that it was affecting business operations. Poking around the native SSIS Event Log data it was really difficult to get any clue as to what part of the process was the bottleneck.

Now I knew that Microsoft had previously released some report packs (link) for the SSIS Event Log data that contained the logic I needed to extract this data. So I took these reports and reverse engineered them into an ETL process to load a new Data Mart (DM) based on the SSIS Event Log data.

Add an SSAS cube and some basic reports and I was now able to analyze the performance of my CDI and ETL processes easily and determine where bottlenecks exist and improvements can be made. Another interesting outcome of this is the ability to measure the improvements of changes to the CDI and ETL processes over time.

This project is designed to be generic so that anyone using SSIS can take advantage of this and gain some insight into their process performance. If you are interested in obtaining a copy of the source code please create a codeplex user account and send me a request on the project site (below).

Hope this helps!
Ben

SSIS Event Log Business Intelligence Codeplex Home – ssisbi.codeplex.com
Codeplex Registration – www.codeplex.com/site/register
San Diego SQL Server User Group – www.sdsqlug.org
SSIS on MSDN – msdn.microsoft.com/en-us/library/ms141026.aspx