Monitor your SSIS Package execution with PowerPivot

I had a interesting week so far, we had a problem in one of our reports, to debug this I wanted to gain insight in how well (or not) our SSIS packages where performing. Eventually the solution was a not in the SSIS execution but a Sproc not being deployed to the production environment (mea culpa 🙁 ).

I started by querying the SSIS logging table used by the SSIS logging provider to determine the execution time of packages and controls. Watching the bulk of data coming up (we run a few packages every minute) and some smart ordering I quickly decided package execution was excellent so the problem had to lie elsewhere.

But while browsing through the data i got the idea wouldn’t it be great when we could see and analyze how package execution performs through the day, or per package, or per day of the week. What better way to do this than by PowerPivot?

I eventually created this PowerPivot app (thats what we call it these days) with 4 different  package execution charts with slice options per package and a detailed report on control execution.

I did not use any DAX or other PowerPivot functions but resorted to good old SQL to resolve my issues, I find that I have problems solving more advanced data issues with PowerPivot, could be because of my inexperience with Excel and DAX, I gave it a try but after a few hours i gave up and resorted to SQL using PowerPivot only as a front end to analyze and gain insight. Rob from PowerPivotPro was kind enough to help me out with my lack of knowledge on PowerPivot DAX and Excel and we are currently examining how i could have solved this with PowerPivot, when i have any word on this i will update this post.

Here’s a screenshot of the charts that I have run against the logging on our test server:

You can also check the control execution on detailed level:

You can download the sheet and try it on your own SSIS environment: http://cid-7f4e0559cc74581a.skydrive.live.com/self.aspx/SSIS%20PowerPivot/SSIS.xlsx

All you have to do is make sure you have logging on you SSIS packages configured, do right mouse button inside the package and click logging and configure it to save data to a database and select the following events:

When you have downloaded the sheet you can edit the connection of the datasource to run against you own database, go to the PowerPivot window, Click on Existing connections, select “ssis log database” connection and edit it to your own server /database which contains the SSIS log table:

Happy SSIS analyzing 🙂 When you make changes or fix bugs (which will undoubtedly be in there) post your change in a comment please 🙂