I got a great question from a student in the December delivery of Expert SSIS. A student asked, “Why wouldn’t you use Environments in Visual Studio (Dev, Test, and Prod), and deploy accordingly the mapped project parameters and package parameters?”

I’ve looked into using SQL Server Data Tools (SSDT, or Visual Studio) configurations in the past and, frankly, could not find a good reason to use them. Since I was asked the question and couldn’t remember why I could not find a good reason to use VS Configurations, I thought I’d run more tests. I especially wanted to see if I could use VS Configurations to allow me to develop SSIS packages with the Protection Level property set to Don’t Save Sensitive.

How Can One Use VS Configurations?

Koen Verbeeck (blog | @Ko_Ver) wrote a great MSSQLTips tip titled Using Visual Studio configurations in SQL Server Integration Services projects. In the tip, Koen shares a good example of how to configure and test a parameter value managed via VS Configurations.

If you’re not familiar with setting up and switching Visual Studio Configurations, please go read Koen’s (excellent) tip. I’ll wait…

Done? Cool.

My Tests

Like Koen, I set up a basic Data Flow Task that reads rows from a Departments table. I initially aimed the connection at an AdventureWorks database and it returns 16 rows:

I set my connection parameters up a little differently so I could test Protection Levels. I created two Project Parameters named ConnectionString and ConnectionStringPassword:

Please note I am using a SQL Server login to connect. Like Koen, I set up two Visual Studio Configurations named AWDW and Test:

There are a couple tricks to externalizing connections metadata for connections that require a password because SSIS will not allow you to mix and match Sensitive and non-Sensitive parameters and properties. If the property is sensitive, the parameter must be sensitive. If the property is not sensitive, the parameter cannot be sensitive.

Configuring the Connection Manager’s Expressions property requires setting expressions for both the ConnectionString and Password properties of the connection manager because, by default, SSIS excludes the Password attribute from connection strings:

After test executions with both configurations, I changed the Protection Level  property of both the project and the package to Don’t Save Sensitive (DontSaveSensitive).

Why?

I was hoping I could use Visual Studio Configurations to execute SSIS Packages using the DontSaveSensitive ProtectionLevel. My results?

I’m still searching for a way to develop SSIS packages using the DontSaveSensitive ProtectionLevel setting; a way that doesn’t require changing the package before deploying it to the SSIS Catalog; a way, in other words, that preserves the goals of a data integration lifecycle management.

Using VS Configurations for Connections Management

In my opinion, it’s acceptable to use Visual Studio Configurations for Connections Management if:

• Your enterprise does not have firewalls between Production, Test, Development and other lifecycle tiers.
• You allow developers to deploy SSIS to Production (not uncommon in small shops where one or two people are the IT department).
• You permit SSIS developers deploy SSIS directly from SQL Server Data Tools (SSDT, or Visual Studio).

Please realize the list above is not an endorsement of these practices. In fact, I responded to the student’s question along the lines of, “I’ve looked into it and chose another path.”

I wrote the book Data Integration Life Cycle Management with SSIS: A Short Introduction by Example to describe ways to manage SSIS development, source control, deployment, management, execution, and monitoring. I’ve built tools – which I discuss in the book – to close gaps I perceive in enterprise SSIS lifecycle management as it ships out of the box. (Most of the tools are free and some are even open source!)

“So How Do You Recommend We Manage SSIS Connections, Andy?”

That is an excellent question. I’m glad you asked!

I recommend robust data integration lifecycle management (DILM). It’s definitely more work, but I think of practicing DILM as a technical investment which is the polar opposite of technical debt. For example, the investment of adding to SSIS packages Script Tasks that surface variable values – especially in loop containers – will pay for itself the first time an error is encountered.

Externalizing connections using Catalog Environments makes sense in DILM if and only if you are aware of the shortcomings of the SSIS Catalog node in SSMS (i.e., once you click the OK button, you cannot use the Script button to generate scripts for either the Catalog Environment or its Catalog Environment Variables). Shameless plug: SSIS Catalog Compare manages this – and all SSIS Catalog-related scripting.

I’ve written about SSIS Catalog Environments and their application in the book Data Integration Life Cycle Management with SSIS and the article titled SSIS Catalog Environments– Step 20 of the Stairway to Integration Services – part of the Stairway to Integration Services at SQL Server Central. And, of course, we cover DILM extensively in the Expert SSIS course delivered in cooperation with Brent Ozar Unlimited and the Immersion Event for SSIS 1 delivered in cooperation with SQLSkills.