SSISDB Reporting with Power BI
Back in 2012 when the project deployment model came out for SSIS, many questions were asked regarding its advantages vs the old legacy deployment model. I still see quite a few customers today who use the old legacy package deployment model. The upgrade/conversion process from package deployment to project is quite straight-forward, and I encourage any slow adopters to migrate their environment. One of the major benefits of the project deployment model is the SSISDB, which is a system created database which tracks many different metrics regarding the SSIS environment. Additionally, with the popularity of Power BI Desktop it becomes very easy to report and understand the Integration Services environment holistically without needing to spend large sprint cycles to implement.
With that in mind, I have built the below Power BI report that quickly reports on the performance of the packages located within the SSIS catalog. There are 2 pages on the dashboard. The first, Overall Executions, shows the overall number of executions that have been tracked within the catalog:
There are various slicers at the top to filter by project, folder, or whether or not the package was run in 32 bit mode. At the top right the dashboard will show the number of packages that have completed successfully or failed by time of day. In the bottom left the median duration of each package is reported. Finally in the bottom right the min available physical memory is reported against the total physical memory in KB. If the min available physical memory drops to 0 this would be a major performance degradation for SSIS.
The second page is a more detailed drill down of the individual executables. Using the various slicers for project, 32 bit run time, and execution result, the user is able to get a glimpse of the 10 longest running executables as of their last runs, and in the bottom right see the trend of those executables over time. Below is a screenshot:
The template is available for download here. When you open the file you will be prompted to enter the server name where the SSISDB instance resides. If you need to modify the dates (the template is pre-populated from 1/1/2016-12/31/2017), you can edit the query by going to “Edit Queries” at the top of the page. Click on the date table, and then under the “Applied Steps” on the right hand side click on Source. Modify the date function from
= DateFunction(#date(2016, 1, 1), #date(2017, 12, 31), null)
To whatever you would like your dates to be.
With the combination of the project deployment model and the above Power BI report, every single individual who uses SSIS in some way should have a complete holistic picture of the performance of their environment anytime it is needed.