Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Reporting with the SQL Server Integration Services Catalog


By:   |   Read Comments   |   Related Tips: More > Integration Services Administration

Attend these FREE MSSQLTips webcasts >> click to register


Problem

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?

Solution

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


Last Update:


signup button

next tip button



About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a BI professional, specializing in the Microsoft BI stack with a particular love for SSIS.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools