For organizations using SQL Server 2012 and newer, the SSIS catalog is the ideal tool for storing, executing, and monitoring ETL logic. The SSIS catalog includes built-in reports that show execution activity for current and historical operations. While these built-in reports are very useful, they have one significant limitation: they can only be viewed from within SQL Server Management Studio.
To get around this, I created a set of reports for internal use at my company. These reports, built using SQL Server Reporting Services, allow easy browser-based access to the activity in the SSIS catalog logging tables. Last month, I decided to open source these reports, and have shared the project on GitHub as the SSIS Catalog Dashboard.
The SSIS Catalog Dashboard
The SSIS Catalog Dashboard is a simple collection of reports that provide insight into the activity within the SSIS catalog. The first of these is the Dashboard report. This report shows a summary of the number of packages that are running or have run in the recent past.
This digest includes clickable links to each category of report execution. Clicking one of these boxes navigates to the Package Execution List report. As shown below, this report shows the status of each recent execution based on status.
The above Package Execution List report has parameters to allow filtering on package, status, and the amount of history to show.
For drilling into the details of a single execution, you can click on the Execution ID from the previous report. This opens the Package Execution Detail report shown below. This detail report can also be accessed directly by passing in a value for Execution ID.
This report shows the execution-level detail for a single execution, including the runtimes and results of each executable. This level of detail can help identify which tasks or components contributed to the package failure.
To dive even deeper into the weeds, you can click one of the links at the top of the data grid. The Show messages link opens another report which displays all of the event messages for this execution. Clicking the Show errors link drills to the same message report, but filters out all but the error messages.
Finally, there is a Package Detail report that shows the most recent executions, as well as a simple graph that displays the runtimes for those executions.
License Information, Contributors, and Future
The SSIS Catalog Dashboard is licensed under GNU General Public License v3.0. This license allows for modification and redistribution as long as the same GNU license terms are conveyed in any derivative copies. Since this is a very new project, I have so far been the sole contributor, but I would welcome feature suggestions or pull requests.
This project still has a bit of a v1 look and feel, and there is room for improvement in both aesthetics and functionality. However, this simple set of reports has eased my burden of monitoring an analyzing SSIS execution history. I’m happy to share it with the community in the hope that it will help others and will evolve through the contribution and input of others.