In Andy’s Excellent SSIS-in-the-Cloud Adventure, Part 1 – Build an ADFv2 IR Instance I wrote about the three steps to configure Azure-SSIS Integration Runtime in Azure Data Factory Version 2 (ADFv2 IR, or SSIS in the cloud). In this post, I’m going to continue by showing you how to deploy, execute, and monitor an SSIS package. I build on the stuff I wrote in Part 1.

Get the SSIS Project

First you need an SSIS project. I built a test project named SimpleTest. You can grab the SimpleTest SSIS project by following this link. The project includes an SSIS package (named Package.dtsx), two project parameters (ConnectionString and ConnectionStringPassword) used to externalize connection properties to the TestDB Project connection manager, and two T-SQL files for creating the TestSource and TestDestination tables.

You will need to:

• Modify the SSIS Project connection string parameter values to match your login credentials, database server, and password
• Connect to your Azure SQL database and execute the T-SQL scripts

Deploying an SSIS Project to ADFv2 IR

Once you’ve setup your ADFv2 IR instance, configured your SSIS project, and added the tables to your Azure SQL database, you can deploy the SSIS project to ADFv2 IR. To do so, right-click the SSIS project name and click Deploy:

The Integration Services Deployment Wizard starts and may display the Introduction screen:

Click Next to open the Select Destination page. Supply the name of your server. Select SQL Server Authentication from the Authentication dropdown and supply your login credentials:

Click Connect to connect to your instance of ADFv2 IR. If all goes as planned, the Path textbox will enable:

Click the Browse button to open the “Browser for Folder or Project” dialog:

Click the New Folder button to open the “Create New Folder” dialog and enter a name for your SSIS Catalog folder:

Click the OK button to close the “Create New Folder” dialog. Note the Catalog Folder now appears and is selected in the “Browse for Folder or Project” dialog:

Click the OK button to configure the SSIS Catalog Path of the SSIS project:

Click Next to display the Validate page:

Click Next to display the Review page:

Click Deploy to deploy your SSIS project to ADFv2 IR:

When deployment completes, the Integration Services Deployment Wizard should appear similar to this:

Execute an SSIS Package in ADFv2 IR using SSMS

To execute an SSIS package in the Azure Data Factory version 2 Integration Runtime using SQL Server Management Studio (SSMS), you must first connect to the Azure server hosting your ADFv2 IR SSIS Catalog. Enter you Server name and authenticate using SQL Server Authentication but do not click Connect (yet):

Click the Options button and select the Connection Properties tab. Enter SSISDB in the “Connect to database” textbox. Please note – failure to connect directly to the SSISDB database will disallow displaying the SSMS Object Explorer “Integration Services Catalogs” node:

Click Connect to connect to the instance. You should see the SSMS Object Explorer “Integration Services Catalogs” node and be able to expand it to show the SSIS project as shown:

To execute the package, right-click the package in the SSMS Object Explorer Integration Services Catalogs node and then click Execute:

When the Execute Package window displays, click the OK button to execute the SSIS package:

The package will begin executing… in a bit.

Monitoring SSIS Package Execution in Azure Data Factory Version 2 Integration Runtime

You can monitor the package’s execution using the SSIS Catalog reporting solution built right into SQL Server Management Studio (SSMS). Right-click the SSIS Catalog node – it’s named SSISDB under Integration Services Catalogs – hover over Reports, hover over Standard Reports, and then click All Executions:

There’s a lot of great information in this reporting solution. My favorite four reports are:

1. All Executions
2. Overview
3. Messages
4. Execution Performance

All Executions

The All Executions report provides a great overview of recent executions, ordered from latest to first from the past week (by default). You can change the time range by clicking the filter icon and changing the date range (and other filters):

Overview

The Overview report provides high-level details about a single execution of a single SSIS package. The Execution Information tablix provides package-start details about the Execution, while the Execution Overview tablix provides status feedback for individual Execution Paths inside the SSIS package:

The Messages report surfaces package execution data sent to the SSIS Catalog logs – usually via SSIS Package Events. You can alter the depth of SSIS Catalog logging at the SSIS Catalog level (not recommended) or the SSIS package level for individual executions (recommended):

The Execution Performance report provides useful information for comparing executions of the same SSIS package over time.

Conclusion

Well, that’s a look at how to deploy, execute, and monitor SSIS packages in the Azure Data Factory Version 2 Integration Runtime!

In Part 3 we take a look at some alternatives for execution, deployment, and monitoring SSIS running on ADFv2 IR.