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 an author and engineer who enjoys building and automating data integration solutions.

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:

image

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:

LogicalSSISCatalogEnvironment

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:

LogicalSSISCatalogReference

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):

image

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:

dilm_6

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

dilm_7

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:

dilm_8

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):

dilm_9

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:

dilm_10

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:

dilm_11

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:

dilm_12

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):

dilm_13

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

dilm_28

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

dilm_15

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:

dilm_16

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:

 dilm_31a

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

dilm_32

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! :)

dilm_33

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

dilm_34

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)”:

dilm_35

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:

dilm_36

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.

dilm_37

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

Published Sunday, January 22, 2017 6:00 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

 

Tom Hogan said:

Hi Andy,

I've found catalog environments / variables useful when I have multiple package that reference that same objects (i.e. servers, file paths, etc.).  I'm not sure how they help with portability.  Can you got into some more details into how environment variables help with that; particularly in relationship to moving from development to production?

Thanks.

March 1, 2017 9:30 AM
 

andyleonard said:

Hi Tom,

  Great question, sir!

  The example that leaps to mind is SSIS developed for regulated industries. I've built a lot of SSIS for government, medical, and financial-related enterprises (sometimes all three at once). It's common for agencies and enterprises in these fields to audit the software for regulatory compliance. Auditors want a document trail describing how the packages were tested prior to deployment to Production. They really like the idea of the SSIS package *not* changing between a User-Acceptance environment sign-off and deployment to Production. They don't mind if external *configuration* metadata changes as long as the tested and approved SSIS package does not change.

  Storing connection strings, source and destination extract file locations, and general metadata about the process in an SSIS Catalog Environment is an option - a good option, in my opinion - for managing these types of requirements. I consider them a good design pattern and a best practice for non-regulatory solutions, as well.

  The off-the-shelf solution for SSIS Catalog Environment management is the Integration Services Catalogs node in the SQL Server Management Studio (SSMS) Object Explorer. Once a Catalog Environment is created and configured, the Environment dialog will not generate a script for the Environment or its Environment Variables.

  I built SSIS Catalog Compare (https://dilmsuite.com/ssis-catalog-compare) to help.

  In this video (https://www.youtube.com/watch?v=NOtciyKGCes) I use SSIS Catalog Compare to promote a "newer" version of an SSIS project from one Catalog to another. SSIS Catalog Compare can also be used to promote Catalog Folders, Catalog Environments, Catalog Environment Variables, References, and Reference Mappings between SSIS Catalog instances. You can also generate scripts (and ISPAC files) for these objects - ready to be sent to a Production DBA for deployment to Production in cases where the Developers do not deploy their own code to Production (also a best practice).

  There's a Command-Line Interface (CLI) utility named CatCompare available that facilitates SSIS code promotion automation. You can deploy changes to a Dev server at the end of each day. A single call to CatCompare can be executed using a scheduling utility (Windows Scheduler, SQLAgent, etc.). The command line would load SSIS Catalog metadata from your Dev server and an Integration server, compare the metadata, and automatically deploy your updates to Dev to the Integration server each night.

  For more information, please visit https://dilmsuite.com/ssis-catalog-compare.

Hope this helps,

Andy

March 2, 2017 4:57 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

News

My Latest Book:

Community Awards



Friend of Red Gate

Contact Me

Archives

Privacy Statement