THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Andy Leonard

Andy Leonard is an author and engineer who enjoys building and automating data integration solutions. Andy is co-host of the Data Driven podcast.

An Example of Data Integration Lifecycle Management with SSIS, Part 2

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):

dilm_17

The Execute package window displays. For this SSIS project, please pay particular attention to the MedicalDatabaseConnectionString parameter value (click the image to enlarge it):

dilm_18

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):

dilm_19

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):

dilm_20

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):

dilm_21

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:

dilm_22

The Overview report contains information about this execution of this SSIS package (click the image to enlarge it):

dilm_23

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):

dilm_24

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?

dilm_26

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):

dilm_27

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

Published Sunday, January 15, 2017 6:00 AM by andyleonard

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

News

My Latest Book:

Community Awards



Friend of Red Gate

Contact Me

Archives

Privacy Statement