Tuesday, March 20, 2012

Package Configuration with Environment Variable

Hi,

I have issues with the Connection Manager in the SSIS package when using package configs thru environment variable.

Here goes..

SSIS package1:

Connections used: devcon1, devcon2 - Dev Env and testcon1,testcon2 - Test Env. Now using all four. Ideally either devcons or testcons should reside at a time.

Environment variable:

Pckg_config = <location of config file which has testcon1 and testcon2>

I need to use only devcon1 and devcon2 in Dev env. In test i need to use only testcon1 and testcon2

Hence i set the values of devcons in devEnv.dtsconfig and testcons in testEnv.dtsconfig

Now i remove both testcons from ssis package. If i try to run the Test Env and my testcons which are marked in testenv.dtsconfig are not found as connections in ssis package then the ssis gives error wanting for those connections.

SSIS maintains the connections in the Connection Manager per package. Although internally it is a pool of connections.

Ideally i should be able to play around with the connection at run time. My package now works, if it is deployed with all the devcons and testcons together. However, ideally it should be either devcons or testcons. I am trying to be more explicable to reach to the masses here.

Am i doing something wrong? All your efforts in solving this puzzle will be greatly appreciated. Please participate.

Thanx,

TusharYou should have only two connections (devcon1 and 2) defined in your package. Set up a single configuration file that sets the two connections. When you deploy to test, you need to create a new copy of the configuration, and alter the connection strings in it to point to your test environment. So you should be using the same package file, but different configuration files in each environment.|||Thanx for responding.

I have followed exactly what you said with my prior set up. I have a devEnv.dtsconfig with devcons and testEnv.dtsConfig with testcons.

The issue is with the package. While creating the package i created it with devcons. Additionally i have testcons which do nothing but sit with the package and get deployed on the server.

If i do not add testcons.....testEnv.dtsconfig gives connection errors....when trying to locate testcons.

Let me know if you need more info...

Thanx again.

Tushar
|||You should have only two connection managers in your package. You should not have testcon1 and testcon2. To create your 2nd configuration file, copy the first configuration file into a new folder - but keep the same name. Open it in notepad, and edit the <ConfiguredValue> tag to set it to your test connection.

When you run the package for test, use the DTEXEC /CONF option to specify the 2nd configuration file.

No comments:

Post a Comment