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.
This is simply fantastic! I was looking at commercial solutions to do this, but could not find anything suitable. Brilliant!!
I noticed that the execution list is always empty when I want to see the currently running packages, although packages are currently running. Then only appears once completed. Any ideas?
Fred, if you’re running it from SQL Server Data Tools, you may have to do a manual refresh of the data. SSDT will cache the result set for better performance during testing and development. Hit the Refresh button after rendering it should force it to retrieve the most current results. Note that running it from the browser should always return the current data, unless you set up report snapshots.
I run it from Power BI Report server. I can see the running packages if I use ‘Execution detail’ report with the ExecutionID. but using the ‘ExecutionList’ does not shows anything. The dashboard shows 1 item running, but then clicking on this item shows no result.
I found why. In dsExecutionList.rsd, I replaced this line AND CAST(end_time AS DATETIME) > DATEADD(HOUR, 0 – CAST(@RecentEventNumberOfHours AS INT), GETDATE()) with AND ( end_time is nulll OR CAST(end_time AS DATETIME) > DATEADD(HOUR, 0 – CAST(@RecentEventNumberOfHours AS INT), GETDATE()))
Running packages don’t have a end_time yet and they were skipped by the SQL command
Fred, great catch! It makes sense now that the running package would show up on the dashboard (which uses a different data set) but not in the execution list. I’m changing the code so that it uses the start date rather than the end date as a date filter.
Kind of new to using other folks code…how do you get the project to open up in VS? the .sln file gives me a cannot load error.
Brandt, if you don’t already have SQL Server Data Tools (SSDT) installed, you will need to do so. If you are running Visual Studio without SSDT, you’ll be able to load the .sln file but it’ll throw an error when trying to load the SSRS project within that solution.
I put together this PowerBI SSIS dashboard last year. Has lots of features. Source code is provided.
https://community.powerbi.com/t5/Data-Stories-Gallery/SSIS-Catalog-DB-Dashboard/td-p/244677
Hi Tim
for SQL 2016 SSISDB we need to change the DataSet “dsExecutionList” the fields: executed_count and worker_agent_id (I changed it to “executed_as_name”) – thank you !
I am unable to run the report
Hi Tim
All my SSIS execution errors messages are not visible in execution reports any idea or solution on how to fix.