In this post I will demonstrate how to use the SSIS Catalog to execute and monitor an SSIS project.
Manual SSIS Package Execution in the SSIS Catalog
To execute an SSIS Package manually using the SSIS Catalog, connect to the SQL Server instance that hosts the Catalog using SQL Server Management Studio (SSMS). Once connected, open Object Explorer and drill into the Integration Services Catalogs node until you locate the SSIS package you desire to execute. I’m going to execute the ProviderGetFile.dtsx package. Right-click the package and click Execute (click the image to enlarge it):
The Execute package window displays. For this SSIS project, please pay particular attention to the MedicalDatabaseConnectionString parameter value (click the image to enlarge it):
If you downloaded the demo project, it includes a SQL Server 2016 backup of a database named Medical. Restore the Medical database to a test instance of SQL Server 2016 (or higher) before proceeding. Then follow these steps to update the MedicalDatabaseConnectionString parameter value.
1. Click the ellipsis to the right of the MedicalDatabaseConnectionString parameter value (click the image to enlarge it):
In the Edit Literal Value dialog, edit the connection string in the Value property. Set the Data Source portion of the connection string to the SQL Server instance to which you restored the Medical database (click the image to enlarge it):
Click the OK button and your Execute Package window should now reflect an overridden value for the MedicalDatabaseConnectionString parameter value (click the image to enlarge it):
Values that are overridden with Literals are shown in bold font.
Monitoring Using the SSIS Catalog Reports Solution in SSMS
Click the OK button to begin executing the SSIS package. You will be prompted with a dialog similar to that shown below. Click the Yes button to view the Overview report:
The Overview report contains information about this execution of this SSIS package (click the image to enlarge it):
The ProviderGetFile.dtsx package takes some time to execute. On my virtual machine, it took about 5.5 minutes (click the image to enlarge it):
Monitoring Using the Catalog Reports Solution from DILMSuite
I really like the SSIS Catalog Reports that are built into SSMS. They are informative and intuitive. I have one complaint, though, and that is that the reports are built into SSMS. Why is that an issue? Some people in the enterprise have a legitimate need to view these reports. As you can see by viewing the screenshots above, the Overview Report can provide operational and historical data regarding the execution of an SSIS package. This information is vital for enterprise data integration.
But SSMS contains other functionality that is, frankly, not vital for many consumers of the Overview Report. The additional nodes highlighted in the screenshot below, for example, are unnecessary for people in non-database-savvy roles within an enterprise.
One may argue – fairly, even – that enterprise security can and should manage access in such a way that users who access the Overview Report do not have access to the highlighted nodes in the following screenshot. We are in violent agreement. But… must we surface these nodes and their accompanying functionality to users who only need to view the Overview Report?
One of the first Data Integration Lifecycle Management (DILM) Suite solutions I built was Catalog Reports. Catalog Reports is a relatively simple and straightforward version of some of the SSIS Catalog Reports embedded in SSMS. The main difference is Catalog Reports is a SQL Server Reporting Services (SSRS) solution.
It’s free.
And it’s open source. Here’s a screenshot of the Overview Report for the same execution above (click the image to enlarge it):
As you can see by mere observation, the reports are not identical. Most of the information included in the SSMS-version of the Overview Report is, however, present in the DILM Suite version. Plus it’s open source, so if you’d like for the report to surface more data or appear differently, you have the source.
Best of all, the DILM Suite version provides no additional access to SQL Server functionality. Must you still manage and maintain SQL Server security? That’s a silly question and the answer is, “Of course!” DILM Suite’s Catalog Reports helps by surfacing only data and functionality required for enterprise data integration reporting.
Conclusion
In this post I demonstrated how to use the SSIS Catalog to execute and monitor an SSIS project.
:{>
—
You might like working with Enterprise Data & Analytics because we like helping people understand the SSIS Catalog.
—
Learn More:
SSIS Academy: Using the SSIS Catalog Day 1 – Create the Catalog and Deploy
SSIS Academy: Using the SSIS Catalog Day 2 – Package Execution and Monitoring
Previous Posts in this Series:
An Example of Data Integration Lifecycle Management with SSIS, Part 0
An Example of Data Integration Lifecycle Management with SSIS, Part 1
Related Training:
SSIS Lifecycle Management (free recording, registration required)
IESSIS1: Immersion Event on Learning SQL Server Integration Services – April 2017, Chicago
IESSIS2: Immersion Event on Advanced Integration Services – May 2017, Chicago
From Zero to Biml – 19-22 Jun 2017, London
Comments