Reporting with the SQL Server Integration Services Catalog

By:   |   Comments (5)   |   Related: > Integration Services Administration


We have deployed several Integration Services (SSIS) projects to the catalog in SQL Server. Packages are being scheduled and run daily. We would like to have an overview of all the executions and whether they were successful or not. We would also like to troubleshoot error messages. How can we achieve this?


The Integration Services catalog was introduced in SQL Server 2012. It is part of the new project deployment model and itís a centralized repository for your SSIS projects and packages. One of the biggest advantages of the catalog is its built-in logging. When you run a package in the catalog, relevant information is stored in the ssisdb database. The level of information logged is controlled by the logging levels. For more information, please check out the following tips:

Having all that logging information available is a great asset, but how do we get it out of the catalog? Luckily, there are several built-in reports available in SQL Server Management Studio (SSMS) to get you started. Keep in mind SQL Server Agent Jobs no longer display error information if a package has failed in the job. Instead, you get a message informing you that all error information is to be found in the catalog.

SSIS Built-in Reports

You can access the reports by right-clicking the SSISDB catalog node in SSMS:

ssis catalog reports context menu

Integration Services Dashboard

This dashboard is meant for daily use. It displays only information for the last 24 hours, so this is the type of report you open in the morning when you arrive at the office. It displays the following information:

  • The number of failed, running, succeeded packages (and other).
  • Detailed info about failed connections.
  • Detailed info about all the execution of the past 24 hours.
  • Links to the other reports in the catalog
SSIS dashboard

If applicable, it shows the error messages if a package failed.

ssis dashboard errors

The dashboard gives a good overview of recent events, but has some disadvantages. Itís not possible to set the time frame of reporting and you cannot filter any of the information.

There are several links to other reports in the dashboard. By clicking on them, you go to filtered subreports. Weíll discuss these in the upcoming sections.

All Executions

This is my favorite report of the catalog and itís the one I open when an error has occurred in a SQL Server Agent Job. It gives a chronological overview of all executed packages in the catalog.

all execution ssis catalog

The biggest strength of the report is you can filter on a number of columns, which allow you to cover big time periods or to look for specific information (for example, all executions of a specific package):

filter all executions

If you have a master package executing several child packages, only the master package will be listed. Letís take a look at the subreports (which are also accessible from the SSIS dashboard).

SSIS Overview Report

In the Overview report, you can find all sorts of information:

  • Specific info about the execution. What was the result? Which environment was used? What was the overall duration? Who started the package?
  • You can also find all the used parameters and their values. If there were any property overrides, those are listed as well.
  • For every task (and subtask) in the package, the duration and result is logged. If you have a master package executing child packages, you can find information about those child packages and their tasks in this table. This list can get quite long if you have lots of child packages and/or lots of tasks. Itís possible no information is logged at all and the list is empty; this depends on your logging level.
overview report ssis catalog

SSIS All Messages Report

After the All Executions report, this report youíll probably frequent most often, as it displays the warnings and error messages of your package executions.

All messages report

The level of detail youíll see in this report strongly depends on your logging level. The report can be empty or just a few lines if thereís an error, or it can be dozens of pages long if you turned on the Diagnostic logging level. If there are a lot of rows, youíll probably want to export the data. The reports are basically Reporting Services anyway. You can do this by right-clicking somewhere in the report and choose your desired export options.

export report

You can for example export to Excel and filter the error messages over there. You can do this for all of the catalog reports by the way.

If you want more context about an error, you can click on View Context.

view context

You get information about the connection managers, variables and parameters, as well as some execution information. Personally, I havenít used this report a lot; most information can already be found in the Overview report.

SSIS Execution Performance Report

This report gives you a breakdown of how well the package performed. You get the duration for the specific execution, but also a 3-month average and the standard deviation. You get more info about previous executions, in the format of a line graph and a table. Depending on your logging level, you can also get more information about data flow performance, if applicable.

execution performance

You can access this report from the Overview report, the All Executions report and the dashboard.

SSIS All Validations Report

Like the All Executions, the All Validations report shows a list of all the validations done in the catalog.

all validations

SSIS All Operations Report

The All Operations report is more interesting from an administrator point of view. It lists out all operations done in the catalog during a period.

all operations

Operations include executing packages, but also validating packages, changing properties, restoring projects and other maintenance tasks:

operations in the catalog

With this report, you can easily monitor user activity in the catalog.

SSIS All Connections Report

The last report shows the connection context of execution errors that have occurred on the SQL Server instance.

all executions

Itís not clear what this report actually does, and I have not been able to reproduce a scenario where this report actually shows data.

SSIS Catalog Views for Custom Reports

If the built-in reports donít satisfy your needs (for example, the number of rows transferred is not included in the reports), you can build your own custom reports. All data is available in the ssisdb database and is exposed through the catalog views:

catalog views

Jamie Thomson has made an open-source reporting pack on top of the catalog, which you can use as a starting point. Keep in mind itís possible to add your own SSRS reports as custom reports into Management Studio. You can find an example in the tip Adding Custom Reports to SQL Server Management Studio.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a seasoned business intelligence consultant at AE. He has over a decade of experience with the Microsoft Data Platform in numerous industries. He holds several certifications and is a prolific writer contributing content about SSIS, ADF, SSAS, SSRS, MDS, Power BI, Snowflake and Azure services. He has spoken at PASS, SQLBits, dataMinds Connect and delivers webinars on Koen has been awarded the Microsoft MVP data platform award for many years.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Wednesday, February 28, 2024 - 10:16:52 AM - Taneil James Back To Top (92022)
Hi Nihal,

I am wondering if you were able to find a solution to your issue? I am facing the same and cannot find a solution.

TaNeil James

Tuesday, January 23, 2024 - 2:14:34 PM - Koen Verbeeck Back To Top (91876)
Hi Nihal,

I'm not sure how I can be of assistance. This is something you might need to take up with Microsoft Support.


Tuesday, January 23, 2024 - 3:45:05 AM - Nihal Back To Top (91873)
Hello Koen Verbeeck,

I am facing issue while generate SSIDB reports from SSMS, SSMS getting closed automatically. Could you please help me into this.

Nihal Jaiswal

Friday, November 9, 2018 - 2:19:32 AM - Koen Verbeeck Back To Top (78199)

Hi Mike,

I've had this error before as well. Make sure you are upgraded to the latest version of SSMS.
In the meantime, a simple restart of SSMS should do the trick (that's what worked for me).


Thursday, November 8, 2018 - 9:26:30 AM - Mike Back To Top (78191)


Greate article!  I am using SQL Server 2016 developer locally and all I see when looking at reports is a bunch of #Error everywhere.  I am logged in with the sa account.

get free sql tips
agree to terms