By: Koen Verbeeck | Comments (5) | Related: > Integration Services Administration
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:
- SSIS Project Deployment Model in SQL Server 2012 (Part 1 of 2) and Part 2
- Logging Level Recommendations for the SQL Server Integration Services Catalog
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:
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
If applicable, it shows the error messages if a package failed.
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.
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):
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.
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.
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.
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.
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.
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.
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.
Operations include executing packages, but also validating packages, changing properties, restoring projects and other maintenance tasks:
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.
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:
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
- If you want to learn more about the project deployment model, you can read more about it in the tips SSIS Project Deployment Model in SQL Server 2012 (Part 1 of 2) and part 2.
- For more information about logging levels, please check out the tips Integration Services Logging Levels in SQL Server 2016 and Logging Level Recommendations for the SQL Server Integration Services Catalog.
- For more SQL Server 2016 tips, you can use this overview.
About the author
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 MSSQLTips.com. 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
