I’ve been tinkering. Again. In this post I share thoughts about a new custom SSIS task I’ve written to set SSIS package variable values from SSIS Catalog Environment Variable values.

The Problem I’m Trying to Solve

One thing I miss about building and executing SSIS from the file system is dtsConfig files. I can hear some of you thinking…

“Why Do You Miss DtsConfig Files, Andy?”

That is an excellent question. I’m so glad you asked! I miss dtsConfig files because they were coupled to the SSIS package at design-time and at execution time. This meant that, when I executed the SSIS package in the designer – SQL Server Data Tools (SSDT) or earlier, in Business Intelligence Development Studio (BIDS) – I was testing package execution with the externalized values.

I enjoyed that this testing was automagic; when I pressed the F5 key, I was not just testing the package with default SSIS variable values, I was including values externalized in dtsConfig files. Back in the days before the SSIS Catalog, we did not have a name for this “deployment model” because there was only one deployment model. When the SSIS Catalog was released with SSIS 2012, the new deployment model was named Project Deployment Model and the previous deployment model was named Package Deployment Model.

Package Deployment Model Still Works

“Why does Package Deployment Model still work, Andy?” My goodness, yall are on a roll today with the excellent questions!

Package Deployment Model still works because of backwards compatibility. Suppose your enterprise data integration with SSIS is built in SSIS 2008 R2 or earlier and you want to upgrade to a newer version of SSIS. Opening an SSIS project in a version of Visual Studio with a newer SSIS extension installed will trigger an update to the latest version of SSIS. Because your SSIS project was built in Package Deployment Model (before Package Deployment Model had a name, even), your SSIS 2008 R2 (or earlier) project will be imported and updated as a Package Deployment Model project.

You have the option of creating Package Deployment Model SSIS projects – even now. I created a Package Deployment Model project to demonstrate:

I set the TargetServerVersion of the SSIS package to SQL Server 2017 (seen in the green box) and I converted the project to Package Deployment Model (seen in the blue box) right after it was created. How? I right-clicked the project and then clicked “Convert to Package Deployment Model”:

 

Once converted, I can display the Package Configurations Organizer from either the SSIS dropdown menu or by right-clicking the white space of the SSIS Control Flow, and then clicking “Package Configurations…”:

The Package Configurations Organizer displays:

An alternative method of displaying the Package Configurations Organizer is to select the SSIS Package by clicking in the Control Flow white space, pressing the F4 key to display the Package properties, navigating to the Configurations property, and then clicking the ellipsis next to the Property Value textbox which contains the text: “(Collection)”:

Andy’s (Potentially Important) Advice

Please note: You may use this alternative to open the Package Configurations Organizer in SSIS packages designed in the Project Deployment Model. And… dtsConfig files may be configured for SSIS packages deployed to the SSIS Catalog using this method. My recommendation, though, is that you not do this. In my humble opinion, mixing the technologies is akin to crossing the streams. Pick a deployment model – Project or Package – and roll with that model’s features.

Unless you have a compelling reason, don’t “jack up SSIS.”
If you do have a compelling reason for jacking up SSIS, over-document.
Future you will thank you, as will anyone attempting to troubleshoot the SSIS package in the future.

One Problem with Project Deployment Model

One problem with Project Deployment Model is: I cannot find a good way to connect SSIS packages to SSIS Catalog Environment Variable values at design time.

I have found ways to accomplish this, I just do not like the ways I’ve found. I feel I am “jacking up SSIS” with the methods I’ve found to date.

This does not mean there are not ways to connect SSIS packages to SSIS Catalog Environment Variable values at design time. 
This does not mean there are not better ways to connect SSIS packages to SSIS Catalog Environment Variable values at design time than using a custom SSIS task.
This just means I haven’t yet identified a way – without jacking up SSIS – to connect SSIS packages to SSIS Catalog Environment Variable values at design time.

An SSIS Catalog Environment Task

At the time of this writing (26 Nov 2021), my version of an SSIS Catalog Environment Task is officially in beta. I may be stretching the definition of “beta” (or maybe even “alpha”) here, but I get to make these determinations because I wrote this code.

You may view a video demonstrating the following configurations and tests in a post titled The SSIS Catalog Environment Task Stream.

The SSIS Catalog Environment Task, like all SSIS tasks, surfaces a name and description property on the General view:

The Settings view surfaces three properties:

1. SourceConnection – which allows developers to create or select an ADO.Net SSIS Connection Manager that connects to an SSIS Catalog database named SSISDB.
2. Folder – which surfaces the collection of SSIS Catalog Folders defined within the SSIS Catalog to which the SourceConnection connects.
3. Environment – which surfaces the collection of SSIS Catalog Environments configured within the SSIS Catalog Folder selected in the Folder property.

The Variables view contains a DataGridView control that surface two DataGridViewComboBoxColumns: Name and ObjectName. These columns need to be renamed. Two challenges to software development are:

1. Naming things
2. Cache invalidation
3. Off-by-one errors

</NotOriginal>

The Name column contains a list of SSIS Catalog Environment Variables defined in the SSIS Catalog Environment selected in Settings. The SSIS Catalog Environment was selected from the list of SSIS Catalog Environments configured in the SSIS Catalog Folder, which was populated with a list of SSIS Catalog Folders from the SSIS Catalog configured on the Settings view. The ObjectName column contains a list of write-able SSIS package variables:

Testing It!

For testing, I added a Script Task to display the values of the User::TestBool and User::TestString SSIS variables:

When executed, the hoped-for outcome is the assignment of the SSIS Catalog Environment Variable values to the values of the SSIS Variable values; for which the mappings are configured on the Variables view. The SSIS Catalog Environment Variable values are shown here in SSIS Catalog Browser (a free utility available at DILMSuite.com):

The expected outcome of this test, in which the SSIS Catalog Environment named p3 is configured and values for the SSIS Catalog Environment Variables named boolev and stringev are mapped into values for the SSIS package variables named User::TestBool and User::TestString, respectively, is the following SSIS package variable values:

• User::TestBool – False
• User::TestString – p3.stringev value

Viewing the Execution Results tab after a test execution of the SSIS package reveals the expected results:

Editing the SSIS Catalog Environment Task Settings view by changing the selected environment to p2:

The expected outcome of this test, in which the SSIS Catalog Environment named p2 is configured and values for the SSIS Catalog Environment Variables named boolev and stringev are mapped into values for the SSIS package variables named User::TestBool and User::TestString, respectively, is the following SSIS package variable values:

• User::TestBool – True
• User::TestString – p2.stringev value

Viewing the Execution Results tab after a test execution of the SSIS package reveals the expected results:

Conclusion

One nice feature of SSIS is the ability to extend functionality with readily re-usable, custom tasks.

In my latest book, titled Building Custom Tasks for SQL Server Integration Services: The Power of .NET for ETL for SQL Server 2019 and Beyond, Second Edition, I share one way to build MSI installer files to facilitate easy installation of custom SSIS tasks in the modern data integration enterprise. I demonstrate deploying custom SSIS tasks to Azure Data Factory SSIS Integration Runtime nodes (aka Azure-SSIS).