CODEWITHSUNDEEP
variablesconfigurationssis
rbransfield
rbransfield

Reputation: 1

Creating a connection string in an Indirect Environment Variable for SSIS

I have been scouring the internet for a clear answer to this question and I haven't been able to find one.

We currently have a SQL configuration enabled across packages which stores everything in a database, (dev or prod). It is a tricky process to execute packages, figuring out which configuration the packages are mapped to, and having to change them during testing. My hope is that we can use an environment variable to always use a specific connection string to the specific sql server configuration we want to use based on the machine.

I have seen ways to set up variables with the connection string hard coded as an environment variable with an additional SQL server configuration after to manage this. I have also seen ways to manipulate the connection string using XML and calling that from a variable, or from the package itself.

Both of these solutions work and very well may be viable solutions. My question that I'm really trying to answer is; Is it possible to develop the entire SQL Server configuration connection through the environment variable? As in combining the connection string with the configuration string so that the one environment variable connects straight to the package configuration? Has anyone tried this? Does it make sense as a solution to the problem?

Upvotes: 0

Views: 4677

Answers (1)

Pondlife
Pondlife

Reputation: 16260

Yes, we have done this and it works fine. Each package has two configurations: the first is an environment variable and the second is a SQL Server table. The environment variable is the connection string for a database containing a table with all the configurations and it is applied to a connection manager. The SQL Server configuration uses that connection manager to connect to the configuration database and retrieve the individual settings.

Because configurations are applied in order, the connection string for the connection manager is set before the SQL Server configuration tries to use it. In summary:

  1. Create your package
  2. Add a connection manager called "Configuration Database" or whatever
  3. Add a SQL Server configuration to the package, use the "Configuration Database" as the Connection, and choose the settings to be stored in the table
  4. Create an environment variable such as SSIS_PKG_CONF with the connection string to be used for the "Configuration Database" connection manager
  5. Add an environment variable configuration to the package, use the SSIS_PKG_CONF variable, and assign it to the connection string for the "Configuration Database" connection manager

Now when you run the package, the configuration proceeds as follows:

  1. The package processes the environment variable configuration
  2. It assigns the value of the environment variable to the "Configuration Database" connection manager's connection string
  3. The package processes the SQL Server configuration
  4. It uses the "Configuration Database" connection manager to retrieve individual settings from a database table

This has worked well for us because we can change the connection string to point at a different source database for the configuration, without changing anything inside the package or the launcher (i.e. the SQL Agent job or script that runs the packages). There are other ways to do it and some people feel strongly that .dtsConfig files are the better option, but we found it easier to manage environment variables than config files

But in the end managing package configurations is a preference and a lot depends on what is easier in your environment with the tools and practices you already have.

Upvotes: 1

Related Questions