How NOT to edit SSIS dtsx packages manually to change config filter in SQL Server configuration schema

2
votes

I have many packages that are using the package configuration with the following way:

-ALL Packages have the XML configuration file that has only one proporty defined. The ConnectionString of the SQL Server connection that holds the configuration table for the rest of the properties

-A SEPARATE SQL Server package configuration for each connection manager in the package.

-Finally i have an SQL Server configuration for all the properties that are specific to this package.

I attach a pic of what i mean:

Yellow is the XML config with the connectionstring, Blue the connectionamangers and purple the package specific.

SCREENSHOT

So with this setup i can:

  • Change the xml file location and just point all the setup in another sql server or another database.

  • Or create different configuration filters in the same config table and try to go into the package and change the filter.

With all the above the problem is that if i do anything from within VS, i am loosing the password in the connectionstring because i am not using the encrypt property. And i dont want to use it...

What are my options? Just go in .dtsx with notepad and chage what i want BEFORE i open the package or before i deploy?

-I dont want to use EncryptSensitiveWithPassword, so:

When i go to package configuration and try to change the ConfoigurationFilter to point to another setting then i am getting to the screen to select the property (connectionstring) and when i finish the DATABASE record for the setting is cleared from the Password= that i have put previously.

So i short what i want:

-No EncryptSensitiveWithPassword in my packages.

-Being able to change configuration from within VS WITHOUT resetting the connectionstring string.

1
configurationssis

1 Answers

1
votes

The recommended way for setting this up would be to store the file location of the dtsconfig file in an environment variable. Then change the dtsconfig to use the environment variable rather than a hardcoded location.

So the nuances of that scenario are this:

  • The password gets blanked out when you resave the xml file (as you pointed out in your question). This is what it is, and it is one of the reasons I never use them.
  • A process (devenv.exe) will cache the values of the environment variables on start up. This means you need to restart visual studio if you make a change to the value of the environment variable.
  • The same issue above applies to the integration services service. This will need to be restarted after you add environment variables. Or when you run your packages, the values will not be found.

The idea is that your dev machine points to a dev instance. Then as you migrate the packages to new environments - QA, Prod, each server has it's own environment variable pointing to its respective dtsconfig file.

As a side note, a similar pattern which avoids the password obliteration would be to add a sql connection manager which points to the server which will load the rest of the configurations. Then set the connection string of this connection manager with an environment variable. The advantage is that you don't have to go copying config files around. This works best with integrated security so you are not storing credentials in an environment config. If you want to be more cryptic about it, you could use a registry entry.