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:
-
Create the Catalog Environment
-
Create and Configure the Catalog Environment Variables
-
Reference the Catalog Environment from a Catalog Project
-
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…”:
Give the environment a name and (optional) description:
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:
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:
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:
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:
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:
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:
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:
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:
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:
Conclusion
SSIS Catalog Environments provide a rich and robust solution for parameter externalization.
:{>
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).
There are multiple admin stored procedures available in the catalog for creating and maintaining environments. You could script it out.
Hi Kinzleb,
Excellent question!
Visual Studio forces its paradigm on SSIS development. Most of the time, that works well – but not all of the time. Like you, I like Catalog Environments. But I see gaps in the lifecycle when using them.
I’ve started addressing some (what I perceive as) the gaps with SSIS Catalog Compare (http://sqlblog.com/blogs/andy_leonard/archive/2015/11/24/ssis-catalog-compare-3-minute-drill.aspx).
:{>
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.
:{>