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

In this post I will demonstrate how to use SSIS Catalog Environments, References,  and Reference Mappings to override SSIS parameter values at execution time.

There three sources of SSIS parameter values:

1. Design-time defaults – these are the parameter values developers use when building the SSIS project and packages.
2. SSIS Catalog Literal overrides – as part of the previous post in this series, An Example of Data Integration Lifecycle Management with SSIS, Part 2, I demonstrated overriding the value of a string parameter using an SSIS Catalog Literal override.
3. SSIS Catalog Reference Mapping overrides – we will focus on using Parameters, Environments, References, and Reference Mappings to override parameter values in this post.

SSIS Parameters, Catalog Environments, and References

At the end of 2016, Kent Bradshaw and I delivered a webinar that covers this topic. It’s called SSIS Academy: Using the SSIS Catalog Day 3 – SSIS Configuration and you can access it for free, although registration is required. In that webinar, Kent and I talked about logical and physical models for SSIS parameters, Catalog environments, and references.

Let’s begin by looking at parameters.

SSIS Parameters

SSIS parameters are available only for SSIS projects developed using the Project Deployment Model (the default project deployment model in SSIS 2012, 2014, and 2016). Parameters may be scoped at the package or project. The image below presents a logical diagram of package and project parameters in an SSIS Project deployed to the SSIS Catalog:

SSIS Catalog Environments and Catalog Environment Variables

Unlike SSIS Parameters, which are configured at design time when developing SSIS packages and projects in SQL Server Data Tools (SSDT), SSIS Catalog Environments are creatures of the SSIS Catalog.

A Catalog Environment is a collection of zero-to-many Catalog Environment Variables. The variables contain Name, Data Type, Description, Value, and Sensitive attributes, as we’ll see in a bit. Although it’s technically possible to create an SSIS Catalog Environment that contains no Catalog Environment Variables, I cannot think of a use case for doing so. Catalog Environments are pretty useless without at least one Catalog Environment Variable. The image below presents a logical diagram of an SSIS Catalog Environment that contains one Catalog Environment Variable:

References

A Reference is a “link” between a single Catalog Environment and a single SSIS Project or Package. The image below presents a logical diagram of Reference between a Catalog Environment and a Catalog Project:

Reference Mappings

A Reference contains zero-to-many Reference Mappings.

One way to describe Reference Mappings: Think of a Reference, then drill down one level at the Catalog Environment to a single Catalog Environment Variable and one level at the SSIS Project (or Package) to a single Parameter. The image below presents a logical diagram of Reference Mapping between a Catalog Environment Variable (contained in a Catalog Environment) and a Project Parameter (contained in a Catalog Project):

Summarizing So Far…

These four objects reside solely in the SSIS Catalog:

• Catalog Environments
• Catalog Environment Variables
• References
• Reference Mappings

These four objects are related in the following ways:

• A Catalog Environment contains zero-to-many Catalog Environment Variables.
• A Reference may be configured between a single Catalog Environment and a single Project (or Package).
• A Reference Mapping may be configured between a single Catalog Environment Variable and a single Project (or Package) Parameter.

You may be thinking, “This is awfully complex, Andy. Why would anyone use Environments and References?”

Why This Complexity?

My argument for embracing (or ignoring) the complexity of SSIS Catalog configuration is straightforward: Externalization – the act of storing execution-time values outside of the object to be executed – is one way to achieve portability, a software development best practice.

As an architect I loathe complexity. Whenever I encounter (or opt to develop) complexity in a software project, I question it. I ask others to question it. I don’t always arrive at the right conclusion, but I challenge complexity at every turn.

I’ve helped several organizations implement SSIS Catalog portability using Environments and References. We all agree that the solution is complex, but most also agree that the complexity is worth the flexibility achieved thereby.

Implementing Catalog Environments and References

If you don’t have the demo project, you can download it here and follow the instructions in this post to deploy it.

To implement using our demo project, open SQL Server Management Studio (SSMS) and connect to the instance of SQL Server that hosts the Catalog containing our demo project. In SSMS Object Explorer, expand the Integration Services Catalogs node, then drill down to our SSIS Project Folder. Right-click the Environments virtual folder and click “Create Environment” as shown:

When the Create Environment window displays, supply an Environment Name (I named mine “envMedicalData”) and optional Environment Description, and then click the OK button:

The newly-created Catalog Environment will appear beneath the Environments virtual folder. Right-click the Catalog Environment and click Properties (or simply double-click the Catalog Environment) to open the Environment Properties editor:

Click the Variables page and begin configuring a new Environment Variable by typing in the Name cell. I configured my Catalog Environment Variable thus:

• Name: “MedicalDatabaseConnectionString”
• Type: String
• Description: (blank)
• Value: An OLE DB connection string aimed at the SQL Server instance for my Medical database.

You may recall we overrode this value in the previous post using a Literal override (click the image to enlarge):

Click the OK button to save the Environment Properties. The Catalog Environment envMedicalData is now created and configured with a single Catalog Environment Variable named MedicalDatabaseConnectionString.

Remember, a Reference is a “link” between a Catalog Environment and a Project (or Package). We will now configure a Reference between our demo project and envMedicalData.

Right-click the Project and click Configure, as shown:

When the Configure <Project> window displays, click the References page. Click the Add button to create a Reference between our project and a Catalog Environment:

The Browse Environments dialog displays. References to a Catalog Environment can either be Relative or Absolute. A Relative reference points to a Catalog Environment that exists in the same Catalog Folder as the SSIS Project. An Absolute references points to a Catalog Environment that resides in any Catalog Folder other than the Catalog Folder that contains the SSIS Project. Since we created the envMedicalData Catalog Environment in the same Catalog Folder (Demo) as our project, we can create either a Relative or Absolute reference to envMedicalData. And it doesn’t matter which we create, they both work the same. The image below shows me configuring a Relative reference:

After selecting the Catalog Environment, click the OK button to return to the Configure window. The Environment Name and Folder are now displayed on the References page (“.” indicates “local” or a relative reference):

Click on the Parameters page and click the ellipsis beside the MedicalDatabaseConnectionString parameter:

When the Set Parameter Value window displays, select the “Use environment variable” option and select the MedicalDatabaseConnectionString Catalog Environment Variable from the dropdown:

Click the OK button to save and close the Set Parameter Value dialog. The MedicalDatabaseConnectionString Project Parameter value property is now mapped, via the reference, to the MedicalDatabaseConnectionString Catalog Environment Variable. The Configure window displays Reference Mappings by supplying the name of the Catalog Environment Variable, underlined, in the Value cell:

Click the OK button to complete the Configuration of our Project.

Test Execution

In SSIS Academy: Using the SSIS Catalog Day 2 – Package Execution and Monitoring we walked through executing an SSIS Package in the Catalog, and monitoring that execution. That’s a good test exercise because it will reinforce what you learned last time. If you’re playing along at home, execute the SSIS Package named ProviderGetFile.dtsx.

When we right-click the ProviderGetFile.dtsx package and click “Execute,” we see something different (click the image to enlarge it). We cannot click the OK button until we resolve the error:

The parameter “MedicalDatabaseConnectionString” 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.

We can remedy this by checking the Environment checkbox and selecting a Reference from the dropdown:

When the Environment checkbox is checked, the Reference dropdown is enabled. Since we configured one reference, we have only one option, the “.\envMedicalData” reference:

Once the reference is configured, the OK button is enabled. We can execute the SSIS Package. We may view the Overview Report – either the version built into SSMS or the Catalog Reports version from DILM Suite. I’m going to use Catalog Reports because, well, I wrote it! 🙂

We see the package is running at the time of this screenshot. Click the Overview link to view the Execution Parameters:

The value supplied to the execution of ProviderGetFile.dtsx’s MedicalDatabaseConnectionString package parameter came from the MedicalDatabaseConnectionString  Catalog Environment Variable of the same name (MedicalDatabaseConnectionString). How can we be sure? Update the value so that the connection remains the same but the connection string value is different. All I’ve changed here is how I reach the Data Source; I’ve updated “vmSql16” to “(local)”:

Re-executing and viewing the Overview Report confirms the MedicalDataConnectionString parameter value is now managed outside the SSIS package in the Catalog Environment Variable named MedicalDataConnectionString:

And here we have the updated value from this later execution.

One More Thing…

I can hear you thinking, “Andy, we sure did a lot of clicking to view those values and configurations in the SSIS Catalog. Is there an easier way to see what’s configured?” Yes. Yes, there is an easier way to view SSIS Catalog configurations metadata. Better yet, it’s free!

It’s called SSIS Catalog Browser (beta) and, like Catalog Reports, is part of the DILM Suite . In the image below, please note you can view:

• The Catalog Environment Variable, data type, and value (the lower circle),
• The (Project) Reference and Reference Mapping (middle circle); and
• The (Project) Parameter Override (upper circle).

The Catalog configuration is surfaced in a single view. You do not have to open additional windows or dialogs to get a complete picture of Project and Package configuration in the SSIS Catalog.

As I mentioned earlier, SSIS Catalog Browser (beta) is free!

Conclusion

In this post I demonstrated how to use SSIS Catalog Environments, References,  and Reference Mappings to override SSIS parameter values at execution time.

:{>

—

You might like working with Enterprise Data & Analytics because we like helping teams learn more about 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
SSIS Academy: Using the SSIS Catalog Day 3 – SSIS Configuration

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
An Example of Data Integration Lifecycle Management with SSIS, Part 2

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