Monitoring Package Executions - SSIS

Auditing SSIS packages in the previous version of SQL Server is a pain. If you don’t have a third-party tool like BI xPress from Pragmatic Works, you have to manually set up auditing or use the built-in logging, which is very limited. Reporting is built into the SSIS catalog in SQL Server. You don’t need to create any reports at all — that is, unless you want a custom report from the SSIS catalog or want a central SSIS auditing database. The auditing built into SSIS is on the SSIS server where SSIS is running, so if you have ten different SSIS servers, you will need to look at each server to read the reports. This reporting is a part of the project deployment model. If you use the older package deployment model, you will need to use the older logging methods.

Built-in Reporting

Several reports are built into SSMS and query the SSIS catalog. The easiest way to view the reports for a package is to right-click on the package and select Reports ⇒ Standard Reports ⇒ All Executions (see below screenshot).

The All Executions report shows the package execution history. This report includes the execution ID, whether it succeeded or failed, the folder in which the package is located, project name, package name, start time, end time, and duration of the package, in seconds. An example is shown in below screenshot.

One of the nicest features of the SSIS reports is the capability to drill through to more detailed reports using the hyperlinks. The first link in the All Executions report is the Overview link. Clicking this link opens the Overview report, which provides a summary of the package’s execution. This report shows each task in the package, including its duration. After drilling down for more detailed information, you can click the backlink in the top-left corner of a report to return to the previous report (see Below screenshot).

The Overview report has two links at the top that you can use to drill down for more detailed information. The first is the View Messages report, which includes details about the package down to the event level. The Messages report is great for troubleshooting a package that has errors. The other link on the Overview report is View Performance. This link opens the Execution Performance report, which shows the trending of the package in terms of duration. You can see this report in below screenshot.

If you return to the All Executions report, you will see that the message and execution performance links are also on this report. Therefore, you can drill through from this report directly to those reports, without going through the Overview report.

A Dashboard report is available also. To open it, right-click on the SSISDB in the SSIS catalog and navigate to Reports ⇒ Standard Reports ⇒ Integration Services Dashboard, as shown in below screenshot.

This Dashboard report provides a high-level overview of the entire SSIS catalog. Using this report, you can quickly see the number of packages failed, running, or succeeded, and other status information. You can drill through to the detail reports by clicking the links on the right, as shown in below screenshot.

Four report options are available: All Executions, All Validations, All Operations, and All Connections. The names of these reports are self explanatory, with each report showing details for that item. For example, if you run validation on a package, it will be displayed on the All Validations report. The All Connections report shows all connections used in the package executions or validations. The All Operations report shows an operation performed on the SSIS catalog, such as executing package executions or validations packages, deploying, and server start-up. Each report also has a filter link, which enables you to filter information based on date, status, package name, project name, ID, and folder name. You can see the Filter Settings dialog in below screenshot.

When you right-click on SSIS packages you will notice there is an option for custom reports. You can develop your own custom reports in Reports Services based on the SSISDB.

Custom Reporting

The reporting built into the SSIS catalog gives you an opportunity to view the execution results of your packages. It even gives you trending reports to compare current package runtimes to previous runtimes. Sometimes, however, you might want a report that isn’t provided by the SSIS catalog. In that case, you can either create your own report in Reporting Services or download an already existing report.

CODEPLEX.COM offers a report pack containing a set of useful reports. You can download these reports at

Monitoring Package Executions - SSIS

Keep in mind that this is an open-source product, so it is not supported by Microsoft or any other vendor.

                         Frequently Asked SSIS Interview Questions & Answers

First download the SSIS Reporting Pack.msi from CODEPLEX.COM. Install the report pack by double-clicking on the install file you downloaded. The default install directory is c:SSIS Reporting Pack. You can change this location during the install.

After downloading the reports, you need to point them to your SSISDB. To do this, you must open them in a SSRS project in SQL Server Data Tools and adjust the Data Source. Then save the reports to a location you can access from the SSIS DB. Now you can right-click on an object in the SSIS catalog and select Custom Reports. Browse to the location where you saved the reports, and you should be able to view them. When you receive a warning that you are about to run a custom report that could contain malicious queries, click Run to run the report. Keep in mind that if you receive an error, there is no support for these reports.

You can also run these reports outside of the SSIS catalog in Reporting Services. You can even schedule the reports to run on a subscription basis, to be delivered to the person responsible for monitoring package executions in your environment. In fact, this option is probably a better idea in terms of reporting. By having the reports delivered outside of SSMS, you don’t need to have access to the SSIS catalog to view the package history. This will be critical when developers need to monitor packages in a production environment and don’t have rights to the production SSIS catalog.

List of Related Microsoft Certification Courses:

SSRS	Power BI
SSAS	SQL Server
SCCM	SQL Server DBA
SharePoint	BizTalk Server
Team Foundation Server	BizTalk Server Administrator