I always appreciate codeplex tools that are available out there to help us Database Developers and Administrators in making our lives much easier. One of my favourite codeplex tools is SSIS Reporting Pack written by Jamie Thomson (blog | twitter). The reason is simple, I use SSIS 2012 a lot this year for the company that I work for; and I find SSIS Reporting Pack makes it easier for me to see the progress of the SSIS projects that I have scheduled to run.

In addition, when there is an issue, (e.g. SSIS package just hung due to ASYNC_NETWORK_IO issue on the source database server), it’s much easier to see when it happened and what the last messages were. Of course, in this example, it wouldn’t say anything that there was an ASYNC_NETWORK_IO issue on the source database server, but the ability to visually see what’s happening and quickly narrowing down the result is the key. The tool is written using SSRS, which means I can also export it to Excel, PDF, etc, if I want to.

The SSRS reports accesses the SSISDB database of the server specified in the “SSISCatalog.rds” Shared Data Source. So, it can only track as far as the data retained in SSISDB. If you have multiple SSISDB instances, you will need to run/deploy separate SSIS Reporting Pack reports for each instance. Now, let’s talk about my favourite features within SSIS Reporting Pack.

Dashboard

It’s simple and easy to see what SSIS projects that have been running on the server and their execution time. This gives me the ability to quickly see how my deployed projects are running in recent times.

From here you can go to “Most Recent Execution” , “Most Recent Failed Execution”, “Folders”, “Executions”, “Longest Executions”.

SSIS Reporting Pack - Dashboard

By the way, if you have any suggestions on what else to put on the dashboards, please log it here at codeplex.

Executions Report

This gives me a quick view of how the executed packages perform within the selected period. I can also narrow it down to only show a specific project or folder. This is a more powerful version of the dashboard with more information to start doing analysis. For example, how often a project fails and see if there is any pattern. You can drillthrough to the project execution that failed (or ran successfully) which will bring up the Execution Report.

SSIS Reporting Pack - Executions

Execution Report

The Execution page provides a detailed list of event messages associated to the Execution. It’s a great way for debugging at package and/or task level. Browsing it by drilling down the package executions to the task level is made easy in this Execution page. In addition, you can also browse previous or next execution of the same package; which is very handy if you have deployed a new version of the package and would like to analyse how they compare.

SSIS Reporting Pack - Execution

Wrap Up

So, if you are using SSIS in SQL Server 2012, give SSIS Reporting Pack a go, which you can download from  http://ssisreportingpack.codeplex.com/

The latest version, which is v0.4 is stable. SSRS Report Server is not required as you can run the report from SSDT. However, having it published to an SSRS Report Server is a better idea. Running the report can sometime take a long time.

If you have used SSIS Reporting Pack, please share your thoughts and comments here.

Further Reading

SSIS Reporting Pack v0.4 – Execution Report updated by Jamie Thomson

SSIS Reporting Pack v0.2 now available by Jamie Thomson

SSIS Reporting Pack – Installation Overview at Codeplex