Managing SSIS Catalog Project Parameter Values

One of the coolest features of the SSIS Catalog is Catalog Environments. Catalog Environments offer a compelling and rich solution for Catalog Project Parameter value externalization and configuration management, whether the parameters are connections or parameter values. Configuring and using Catalog Environments involves four steps:

  1. Create the Catalog Environment
  2. Create and Configure the Catalog Environment Variables
  3. Reference the Catalog Environment from a Catalog Project
  4. Map Catalog Environment Variables to Catalog Project Connections and Parameters

Create the Catalog Environment

Create an SSIS Catalog Environment by connecting to an SSIS Catalog using SQL Server Management Studio (SSMS) Object Explorer. Expand the Integration Services Catalogs node, the SSISDB node, and the desired folder node. Right-click the Environments virtual folder and click “Create Environment…”:

CatalogEnvironmentsCreateEnv

Give the environment a name and (optional) description:

CatalogEnvironments_Create

Click OK to create the Catalog Environment.

Create and Configure the Catalog Environment Variables

Return to SSMS Object Explorer and double-click the Catalog Environment to open it for editing. Add and configure Catalog Environment Variables on the Variables page:

CatalogEnvironments

Click OK to save the Catalog Environment configuration changes.

Reference the Catalog Environment from a Catalog Project

In SSMS Object Explorer, right-click a Catalog Project and click “Configure…” to create a reference between the Catalog Project and Catalog Environment:

CatalogEnvironments_ConfigureProject

When the Configure window opens, add a reference to a Catalog Environment on the References page. Catalog Environments are scoped at the SSIS Catalog Folder level. You can reference Catalog Environments in the same Catalog Folder as the Catalog Project or in a different Catalog Folder:

CatalogEnvironments_SelectEnvironmentReference

Once referenced, the Catalog Environment will appear in the Reference grid on the Catalog Project Configure References page. The “.” in the Environment Folder column indicates the Catalog Environment resides in the same Catalog Folder as the Catalog Project:

CatalogEnvironmentsReferences

Map Catalog Environment Variables to Catalog Project Connections and Parameters

On the Parameters page of the Configure window, you can map Catalog Environment Variables to values by clicking the ellipsis on the Parameter grid row that corresponds to the parameter you wish to configure:

CatalogEnvironments_ConfigureParameter

Clicking the ellipsis opens the Set Parameter Value dialog. Click the “Use environment variable” option and select the Catalog Environment Variable from the corresponding dropdown:

CatalogEnvironments_ConfigureParameter_2

The dropdown list is filtered to only display Catalog Environment Variables of a data type compatible with the Catalog Project Parameter value.

Successful mapping is indicated by the underlined name of the Catalog Environment Variable in the Parameter Value column of the Parameters grid:

CatalogEnvironments_ConfigureParameter_3

Catalog Project Parameter Manual Overrides

You can also manually override the value of a Catalog Project Parameter by clicking the ellipsis and select the “Edit value” option:

CatalogEnvironments_OverrideParameter

When a Catalog Package Parameter value is manually overridden using the Edit value function, it displays in Bold font in the Parameter Value column of the Parameters grid. Catalog Package Parameter values that are not mapped to a Catalog Environment Variable or manually overridden are configured to execute with their design-time default values and the text in the Parameter Value column of the Parameters grid is not decorated:

CatalogEnvironmentsParameters

Conclusion

SSIS Catalog Environments provide a rich and robust solution for parameter externalization.

:{>

Andy Leonard

andyleonard.blog

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

4 thoughts on “Managing SSIS Catalog Project Parameter Values

  1. Hi Andy,
    Do you have any thoughts around a good way to put the four steps you outlined into source control and make them part of a repeatable deployment?
    We’ve been using catalog environments for a while, and while I think they can be useful, I’ve also found that the process of setting them up from within the management studio GUI is pretty error prone… particularly in the 4th step you outline (Map Catalog Environment Variables to Catalog Project Connections).

  2. There are multiple admin stored procedures available in the catalog for creating and maintaining environments. You could script it out.

  3. Hi Stan,
      I’ve explored the SSISDB admin stored procedures available in the SSIS Catalog and the methods available in the SSIS .Net assemblies. As always, I am sure there are other ways to accomplish externalization, and each has benefits. Wolfgang Strasser (https://workingondata.wordpress.com/) presents regularly on the same topic, and he does things differently than me. I would love to learn more about how you use SSIS .Net assemblies and the SSISDB admin stored procedures, sir.
      One thing I didn’t mention in this post is (what I consider) missing native support in the Integration Services Catalogs node of SSMS Object Explorer for Catalog Environments, References, Overrides, and Mappings – especially after they’re created. If you don’t click the Script button right after configuring everything – but just before clicking the OK button – the opportunity for GUI-supported script generation appears lost forever. You can re-open the Catalog Environments, References, etc. after creation / configuration and click the Script button, but you will either get an empty script or nothing in response (depending on which GUI you’ve opened).
      You may still generate scripts for transferring environments, variables, overrides, references, and mappings; but you must do so manually – as far as I know. I’ve seen several solutions for achieving this, but I haven’t seen a visual / GUI solution… until recently… This is one of the reasons I built SSIS Catalog Compare (http://sqlblog.com/blogs/andy_leonard/archive/2015/11/24/ssis-catalog-compare-3-minute-drill.aspx). If you have a few minutes, I’d appreciate your feedback on the utility.
    :{>

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.