The Problem I am Trying to Solve

If you configure an Azure Data Factory Execute SSIS Package activity using the Folder, Project, Package, and Environment dropdowns found on the Settings tab, execution of the pipeline will likely succeed. If you then edit the Settings to use “Manual entries” and some of the parameters on the “SSIS parameters” tab are used to override connection-related values, execution of the pipeline will likely fail.

The solution: Manage connection-related parameter value overrides. Options include:

• In the SSIS Catalog, reference an environment, and then map environment variables containing the desired override values to Connection Manager properties via Expressions. Then remove the connection-related overrides shown on the “SSIS parameters” tab of the ADF Execute SSIS Package activity. (I explain the ADF portion this option below)
• Use Azure Data Factory pipeline or global parameters to override connection-related parameter values.

Parameterization – or externalization – is a complex part of software design. In data engineering software, the topic is made more complex by the many moving parts between the software and the database.

In my humble opinion, this post scratches the surface by addressing a common use case, supplying one simple solution.

Short version: Data engineering is hard.

A Story

There you are, doing you job, minding your business, building an Azure Data Factory (ADF) pipeline designed to call an SSIS package using the Execute SSIS Package activity. Since your Azure-SSIS integration runtime (Azure-SSIS IR) is running, you use the Folder, Project, Package, and Environment dropdowns to configure the Settings tab as shown here:

The SSIS Package is stored in an SSIS Catalog configured to work with an Azure-SSIS integration runtime (Azure-SSIS IR). In the example shown in the image above, the Azure-SSIS integration runtime is named “azureSSISCatalog” and you can garner details of its configuration by clicking the Manage page (1) and then clicking “Integration runtimes” (2):

Note: In order to see details of the Azure-SSIS IR configuration, the IR must first be stopped:

Click the IR link shown above to view configuration details:

Ok, back to your Execute SSIS Package activity pipeline…

You click the Debug button and, after a few seconds, the pipeline Output tab reports the Execute SSIS Package activity pipeline succeeded:

Cool? Cool. I can sense some of you thinking,

“So What’s the Problem, Andy?”

I’m glad you asked.

The ADF Execute SSIS Package Activity tab I neglected to show you (until now) is the “SSIS parameters” tab:

The “SSIS parameters” tab displays a list of SSIS parameters (writes Captain Obvious). All parameters scoped at the SSIS Project level will be displayed whenever any SSIS Package from said project is configured for execution by the Execute SSIS Package activity. All parameters scoped at the SSIS Package level – for the configured package (only) – will also be displayed on the “SSIS parameters” tab.

The parameters on the “SSIS parameters” tab shown above are Project-scoped and they are all related to Project-scoped Connection Managers (the “related” part is managed by mapping Connection Manager properties to Project Parameters at SSIS design-time). The parameters are part of the SSIS project deployed to the SSIS Catalog – the Catalog which the Azure-SSIS IR is configured to use.

Remember this part: This is important for another part of our discussion.

When the ADF Execute SSIS Package activity executes, it passes these connection-related values to the SSIS package. You can verify this by looking at the Overview report in the SSIS Catalog Reports (in SSMS):

The Parameters Used tablix in the SSIS Catalog Reports Overview Report (shown above) displays parameter values used by the package in this execution. The list includes the six connection-related parameters displayed on the ADF Execute SSIS Package activity “SSIS parameters” tab in the image preceding the image above (I really hope that sentence makes sense…).

Return to ADF. Click the Execute SSIS Package activity, and then click the “Settings” tab.

Click the “Manual entries” checkbox, and then click the Debug button to test-execute the pipeline, as shown:

The expected result is the pipeline test-execution fails:

Why does the test-execution fail?

The login failed for the first connection manager the test-execution encountered. In this case, the first connection is named “source” even though you cannot see the connection manager name in the portion shown in the image above.

I can hear some of you thinking,

“That error makes no sense, Andy!”

I found this error confusing, too. Why? One reason was I made an assumption that the SSIS Catalog reference (a reference is an SSIS Catalog configuration setting that connects an SSIS project to an SSIS Catalog Environment) would override any values being sent from the ADF Execute SSIS Package activity to the SSIS Project / Package execution call.

One of the values I thought would be overridden was the “source” connection manager’s UserName property. My assumption was wrong.

Ok – to be fair – my assumption was only half-wrong. We will get to why my assumption was only half-wrong in a minute. It’s worth noting that half-assuming is still assuming, though; and half-assuming makes for a messy abbreviation…

Moving on.

My assumption was incorrect, but I understand why I made it. My assumption grew out of my (correct) understanding of how an SSIS package executes in the SSIS Catalog – specifically the order of operations for how overridden values supplant default values.

The incorrect part of my assumption? I’m about to spill those beans.

Return to ADF. Click the Execute SSIS Package activity, and then click the “SSIS parameters” tab (just like we did earlier):

Notice anything different about this image and the image from the last time we clicked the “SSIS parameters” tab? I notice parameter configurations appear more-enabled.

Here are screenshots placed side-by-side for easy comparison:

Do you see why I say the current “SSIS parameters” tab view appears more-enabled?

First, the Fix

To fix the manual entries-configured Execute SSIS Package activity failure, select the connection-related parameters shown on the “SSIS parameters” tab, and then click the Delete button:

When complete, the “SSIS parameters” tab should appear as shown below. Click the Debug button to test-execute the pipeline:

Test-executing the pipeline, with Manual Entries configured for the “Package path” and “Environment path” properties and the connection-related SSIS parameters deleted, now succeeds:

Second, the Explanation

“What was all that jib-jab” about half-assumptions and the like, Andy?” This is where I explain.

My assumption was:

I made an assumption that the SSIS Catalog reference (a reference is an SSIS Catalog configuration setting that connects an SSIS project to an SSIS Catalog Environment) would override any values being sent from the ADF Execute SSIS Package activity to the SSIS Project / Package execution call.

The reason I classify this a “half-assumption” was my assumption is incorrect when I configure the Execute SSIS Package activity using the Folder / Project / Package / Environment dropdowns, but correct when I instead configure the Execute SSIS Package activity using “Manual entries.”

Using Folder / Project / Package / Environment Dropdown Configuration

When I use the Folder, Project, Package, and Environment dropdowns to configure the Execute SSIS Package activity, values for the six Project-scoped parameters (in my particular SSIS package) are supplied to the “SSIS parameters” tab.

When I use the Folder, Project, Package, and Environment dropdowns to configure the Execute SSIS Package activity, I cannot delete parameters from the “SSIS parameters” tab. This is why the “SSIS parameters” tab appears “less-enabled.”

However, I may update parameter values, as shown here:

When I test-execute the pipeline after this edit, the test-execution succeeds:

Viewing the SSIS Catalog Overview Report in SSMS reveals the sourceConnectionString SSIS parameter value reflects the edit I made on the ADF Execute SSIS Package activity “SSIS parameters” tab:

Some important points to remember when configuring the Folder, Project, Package, and Environment dropdowns for the Execute SSIS Package activity:

1. The “SSIS parameters” tab will reflect parameters used in the configured SSIS package.
2. Although the parameters on the “SSIS parameters” tab cannot be deleted, their Values may be edited.
3. The SSIS package will be executed using the values sent from the “SSIS parameters” tab of the Execute SSIS Package activity, overriding all overrides – including Connection Manager Expressions that map parameter values to connection manager properties, and even Reference mappings that override these same parameters with SSIS Catalog Environment Variable values.
   1. For more information about overriding SSIS parameters with SSIS Catalog environment variable values, see SSIS Catalog Environments– Step 20 of the Stairway to Integration Services.

Using Manual Entries

When I check the “Manual entries” checkbox on the “Settings” tab of the Execute SSIS Package activity, values for the six Project-scoped parameters remain available on the “SSIS parameters” tab, and the SSIS parameters appear “more-enabled.”

However, if the pipeline is executed with connection-related SSIS parameters in place, the Execute SSIS Package activity will fail.

One Last Thing

A friend pointed out to me the “information blurb tooltip” next to the “Manual entries” property on the “Settings” tab:

The blurb reads:

“Check to enter your package/environment paths and existing SSIS parameters/connection managers manually. Uncheck to browse and select your folders/projects/packages/environments from SSISDB and assign values to your existing SSIS parameters/connection managers – This requires your running Azure-SSIS Integration Runtime.” (emphasis mine)

Had I read this blurb beforehand (I did not), I may would have understood checking / unchecking the “Manual entries” checkbox impacts more than just the “Package path” and “Environment path” properties.

I may complain the functionality seems non-intuitive to me, but I cannot say I wasn’t warned.

Lesson learned.