Andy’s Excellent SSIS-in-the-Cloud Adventure, Part 3 – Execution, Monitoring, and Deployment Alternatives

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 Andy’s Excellent SSIS-in-the-Cloud Adventure, Part 2 – Deploy, Execute, and Monitor an SSIS Project I wrote about deploying, executing, and monitoring execution. In this post, I’m going to continue by showing you some alternative methods for executing, monitoring, and deploying SSIS packages running in ADFv2 IR.

If you haven’t read the first two posts, I recommend you do so before attempting any code in this post.

Execution Alternative – Scripting

As with SSIS packages executing in a local SSIS Catalog, you can also script the execution of ADFv2 IR SSIS packages. Recall from Part 2 how to execute a package using SSMS? The alternative process is identical – to a point.

As before, 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 Script button to script execution of the SSIS package:

The script generated when I click the Script button is:

Declare @execution_id bigint
EXEC [SSISDB].[catalog].[create_execution]
 , @execution_id=@execution_id OUTPUT
 , @folder_name=N'TestFolder'
 , @project_name=N'SimpleTest'
 , @use32bitruntime=False
 , @reference_id=NULL
 , @useanyworker=True
 , @runinscaleout=True
Select @execution_id
DECLARE @var0 smallint = 1
EXEC [SSISDB].[catalog].[set_execution_parameter_value]
 , @object_type=50
 , @parameter_name=N'LOGGING_LEVEL'
 , @parameter_value=@var0
EXEC [SSISDB].[catalog].[start_execution] @execution_id,  @retry_count=0

The next step is easy – execute the script! After some time – I’ve seen this range from a few seconds to more than a minute – the SSIS package will begin executing and the query will return the @execution_id parameter value:

If you’re familiar with executing SSIS packages from scripts in on-premises, pre-Scale Out SSIS Catalogs, you’ll note a few differences in this script. The @runinscaleout, @retry_count, and @useanyworker parameters are used in SSIS Scale Out scenarios. For more information on these parameters please see the SSISDB.catalog.create_execution and SSISDB.catalog.start_execution pages.

If you comment-out or delete these parameters – @runinscaleout, @retry_count, and @useanyworker – the SSIS package will execute.

Monitoring Alternative – Catalog Reports

The reporting solution built into SQL Server Management Studio (SSMS) is cool. I like it. I just don’t like that it’s stuck in SSMS. Why? In order to give someone access to near-real-time information regarding the execution status of an SSIS package, I have to install SSMS somewhere where they can reach it – and grant them rights on the server to access the information – probably for Production. If I put on my principles-of-least-privilege hat and think about this some, I get “no, no, and heck no!”

What are your options if there’s a less-SQL-Server-savvy stakeholder in your enterprise who has good cause to view this information? One option is to build your own SQL Server Reporting Services (SSRS) reporting solution. Or you can use the SSRS solution I built. It’s called Catalog Reports and it’s not only free, it’s open source!

The reports shown below mirror those in the SSMS solution:

  • Execution
  • Overview
  • Messages
  • Performance

If you don’t like something, you can change it – that’s the beauty of open source. The T-SQL is contained within the reports in the Catalog Reports solution.

Deployment (and Configuration) Alternative – SSIS Catalog Compare

Note: Most of the functionality described in this post is part of the current version of SSIS Catalog Compare. Connectivity to Azure Data Factory Version 2 Integration Runtime is not currently available, but will be available in an upcoming release.

Consider the following “life and shift” scenario: You migrate your data warehouse to Azure SQL DW and want to migrate some of your data integration with SSIS to the cloud as well. Your SSIS Catalog projects are configured and tested on your on-premises servers. Wouldn’t it be nice if there was some way to migrate SSIS projects, packages, environments, references, and other configurations to the cloud?

There is!

SSIS Catalog Compare is built for this. I have the project we used in Part 2 working big as life in vmSQL17\Dev’s SSIS Catalog. I want to move it to my Cloud Catalog named I have a couple options using SSIS Catalog Compare:

Option One: Script Everything

I can certainly script my folder, then export the ISPAC file for the project, then generate scripts for the environment, reference, and reference mappings; beginning with the Folder script:

Or I can script the folder and all its dependent objects with a single click:

The scripts are numbered in order of dependency and are ready to be deployed:

Option Two: Deploy It!

Or I can simply deploy directly from one SSIS Catalog – the one on-premises in this case – to another SSIS Catalog – the one in ADFv2 IR:

I choose Deploy Folder to deploy the folder alone:

I could just as easily select the option to “Deploy Folder and Contents” and deploy the folder, the project(s), environment(s), reference(s), reference mapping(s), literal override(s) – everything.

I decide to deploy the ISPAC file (exported using SSIS Catalog Compare) next. The folder has been deployed in script 1 so it’s already there for me to specify during deployment:

Done and done:

A compare at this point reveals a lot of differences in my Catalog projects:

Let’s start with the Environment. I’ll use the script I created earlier and edit it so the ConnectionString parameter values are correct (click to enlarge):

Note that the ConnectionStringPassword_1 parameter contains no value. SSIS Catalog Compare is designed to never write your sensitive values to a script.

I really like the messages these scripts output. They are built for copying and pasting into the Notes field on a ticket:

Back for another compare:

The environment is there but the ConnectionString variable values are different. That’s good, actually – I want them to be different! But I don’t want differences in my compare to trip me up. So I click View–>Options and select the Ignore Environment Variable Values option:

A re-compare now ignores different values for the Environment Variables. That’s good and bad. My ConnectionStringPassword value needs to be updated in the cloud…

So I use SSIS Catalog Compare to update this sensitive value:

Carefully enter the correct password:

Next I want to migrate the reference and reference mappings. I could easily do this with the script, but it’s kinda cool to use SSIS Catalog Compare:

A quick re-compare and boom – no more differences:

If I deselect the Ignore Environment Variable Values option, I see the source of “color-ized” nodes – they’re all related to the different value for the ConnectionString Environment Variable:

There you have it, another way to lift and shift SSIS projects to Azure Data Factory Version 2 Integration Runtime.

SSIS Catalog Compare is a for-pay product and part of the DILM (Data Integration Lifecycle Management) Suite.

Catalog Browser Is Free

Catalog Browser is a free product from the DILM Suite that surfaces a unified view of an SSIS Catalog – either on-premises or in ADFv2 IR. You can think of Catalog Browser as a limited, single-tree version of SSIS Catalog Compare:

I hope this series is demonstrating the usefulness of Azure Data Factory Version 2 Integration Runtime. It’s an exciting time to be doing this kind of work! And there’s more cool stuff to come…

In Part 4 we take a look at using an SSIS Execution Framework alongside Azure Data Factory Version 2 Integration Runtime!


Andy Leonard

Christian, husband, dad, grandpa, Data Philosopher, Data Engineer, Azure Data Factory, SSIS, and Biml guy. I was cloud before cloud was cool. :{>