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 a Data Philosopher at Enterprise Data & Analytics, an SSIS Trainer, Consultant, developer of the Data Integration Lifecycle Management (DILM) Suite, a Business Intelligence Markup Language (Biml) developer and BimlHero; SQL Server database and data warehouse developer, community mentor, engineer, and farmer. He is a co-author of SQL Server Integration Services Design Patterns, and the Stairway to Integration Services. Keep up with Andy, join his mailing list!

Broken References in the SSIS Catalog

It’s possible to create a broken reference in the SSIS Catalog. What’s a “broken reference?” Let’s begin by explaining SSIS Catalog References.

I wrote a lengthy explanation of SSIS Catalog References and Environments in SSIS Catalog Environments – Step 20 of the Stairway to Integration Services at SQL Server Central.
Microsoft has a good article on SSIS Catalog Environments and References called
Create and Map a Server Environment.

An SSIS Catalog Reference connects an SSIS Project to an SSIS Catalog Environment. Once an environment is referenced by a project, environment variable values can be mapped to project parameter, package parameter, and connection string values. Multiple references-per-project can be configured, but at runtime only one Reference may be selected for use with execution.

We can demonstrate a broken reference by creating a new environment in an SSIS Catalog. Connect to an instance of an SSIS Catalog using SSMS. Expand the Integration Services Catalog node in Object Explorer. Expand the SSISDB node and a folder node. Right-click the Environments folder and click Create Environment:

BrokenEnv_1

The Environment requires only a name and I named this one “EmptyEnvironment”:

BrokenEnv_2

Note: Were I using EmptyEnvironment for externalization, I would create and configure Environment Variables.

Next, right-click a project and click Configure. Click the References page and then click the Add button. In the Browse Environment dialog, navigate to EmptyEnvironment, select it, and click the OK button:

BrokenEnv_3

A referenced environment appears as shown:

BrokenEnv_4

Note: Were I using EmptyEnvironment for externalization, I would map Environment Variables to Parameter and / or Connection String values on the Parameters page.

Return to the Environments virtual folder. Right-click EmptyEnvironment and click Delete:

BrokenEnv_5

The Delete Object window displays. Click the OK button to delete the environment:

BrokenEnv_6

The Environment is deleted with no indication or warning that this Environment is referenced by an SSIS Project in the SSIS Catalog.

We just created a broken reference in the SSIS Catalog’s metadata.

“Is this a bug?”

I don’t think so. The design of the SSIS Catalog allows quite a bit of flexibility in managing references and referenced environments.

Let’s query the SSISDB.catalog.environment_references view. Here we find the reference_id value. (Reference_id is used when the intent to execute  is created by the SSISDB.catalog.create_execution stored procedure.) Examining our EmptyEnvironment row shown below, we find a project_id field and value (58 in this example), which is what we might expect for a Catalog Reference that connects a Project and an Environment.

But we don’t find an environment_id field.

Instead, we find two attribute fields named environment_folder_name and environment_name, and a third attribute named reference_type which tells us whether the reference is to a Catalog Environment in the local Catalog Folder (Relative) or to a specified Catalog Folder (Absolute):

BrokenEnv_8

This means we can fix a broken reference fairly easily by creating (or deploying) an SSIS Catalog Environment to the referenced Catalog Folder.

I can hear you thinking…

“How Do I Detect This Condition Exists, Andy?”

I’m glad you asked! The answer is, “It depends on when the condition was introduced.”

If manually executing a package with a broken reference from the SSIS Catalog node in SSMS, we will see a message similar to this:

The parameter "SensitivePackageParameter" is configured to use an environment variable, but no environment has been selected.  Check the "Environment" checkbox and specify the environment to use, or specify a literal value for the parameter.

BrokenEnv_9

If we are scheduling a package with a broken reference to execute using SQL Agent, we will see a message similar to that shown here:

Parameter "SensitivePackageParameter" is configured to receive a value from an environment variable named "SensitiveStringValue" but there is no environment variable named "SensitiveStringValue" in environment "_env\env2".  Select a different environment, or use a literal value for the parameter. (Microsoft.DatatransformationServices.DTSExecUI.Controls)

BrokenEnv_10

If the reference was broken after the SSIS package execution was scheduled, we may see an error similar to that shown below in the SQL Agent log for the job step that attempted to execute the SSIS package:

Failed to execute IS server package because of error 0x80131904. Server: vmSql16\Test, Package path: \SSISDB\Test\ParametersTest\SensitiveTest.dtsx, Environment reference Id: 35.  Description: The environment 'env2' does not exist or you have not been granted the appropriate permissions to access it.

BrokenEnv_11

There are other indications, but the scenarios above are where I usually encounter broken references.

Another way to detect broken references is to use SSIS Catalog Browser,  a free utility from DILM Suite. In beta at the time of this writing, SSIS Catalog Browser provides a rich view of the SSIS Catalog in a single treeview. Broken references are indicated by gray text and a tooltip that identifies them as broken as shown here:

BrokenEnv_12

SSIS Catalog Browser is free and you can download it here.

“How Do I Fix a Broken Reference, Andy?”

One way to fix a broken reference is to simply create or deploy the environment to the SSIS Catalog. Because environment_references are “connected” to projects by folder name and environment name (in the SSISDB.internal.environment_references table), broken references are relatively easy to fix.

“Only You Can Prevent Broken References” – Smokey the Bear, Andy, circa 2016

Like SSIS Catalog Browser, SSIS Catalog Compare detects broken references.

BrokenEnv_7

Did you know SSIS Catalog Compare can help prevent broken References in the first place? It can!

If you’re using SSIS Catalog Compare to manage your Data Integration Lifecycle, you can delete an Environment from the Catalog Browser treeview:

BrokenEnv_13

SCC will ask you if you’re sure:

BrokenEnv_14

If you click the Yes button, Catalog Compare will check to see if the Environment is referenced by an SSIS Project. If so, it warns you:

BrokenEnv_17

If you click the Yes button, you will create a broken reference. If you click the No button, SSIS Catalog Compare displays a message informing you that you canceled the Delete Environment operation:

BrokenEnv_16

Warning: Gratuitous Profit-Mongering! Sales Pitch Attempted Help:

I am admittedly the World’s Worst Sales Person. I think it’s because I’m overly-focused on helping people.  With that disclaimer…

As you can tell from the screenshots, SSIS Catalog Compare v1.5.2 is a beta / test version at the time of this writing. It hasn’t been released yet. Testing continues and I hope to release this version soon. I am also testing the first version of a Catalog Compare CLI (Command-Line Interface) named CatCompare (check out the 3-minute CatCompare preview video). I intend to release CatCompare with the new release of SSIS Catalog Compare and offer two pricing tiers:

  1. SSIS Catalog Compare + CatCompare for $???
  2. SSIS Catalog Compare only for $295USD

Right now, you can purchase SSIS Catalog Compare v1.0 for $295. If you purchase SSIS Catalog Compare before I release the next version, you can upgrade to the new version of SSIS Catalog Compare and get CatCompare for free. But only if you make the purchase before the release. Ithoughtaboutwritingsomefineprinthereandrunningallthewordstogethertotrytosimulateinwritingthosedisclaimersyouhearattheendofcommercialswherethere’ssomecatchaboutprice,availability,orsomesuch.Thereisnocatch.Sorry.ThisisthebestIcoulddo…

You can learn more here.

</AttemptedHelp>

You might like working with Enterprise Data & Analytics because we help you catch broken references.

Learn More:
Data Integration Lifecycle Management (DILM) Suite – free (mostly) and not-free software to help you manage SSIS in the enterprise.
Deploying SSIS Projects to a Restored SSIS Catalog (SSISDB)
Coming Soon: A Command-Line Interface for Managing SSIS Catalogs
Stairway to Integration Services

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

Need help implementing an SSIS solution?
Contact Enterprise Data & Analytics today!

:{>

Published Tuesday, December 06, 2016 10:33 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