Package Configuration with Environment variables

Question

Package Configuration with Environment variables

tushar-324804

SSC Veteran

Points: 277
More actions
August 8, 2007 at 9:16 pm

#121022

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,

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Crispin Proctor SSCoach Points: 18161 More actions · Answer 1

SSIS binds a component or task to a connection manager at design time and this cannot be changed at run-time.

If you have a package that is to be run on two different environments, you need to have one connection manager with a common name. i.e. Not include the type of connection. i.e. testcon or devcon. Maybe just con.

Your config file will be loaded and the setting spulled from it as the package starts to run. it will look for a connection manager named xxx and try set it's parameters. You would have the config file on the two servers and edit it accordingly.

You can change the connection string of a connection through expressions at run time to point to a different server / DB.

if a config file is specified and the objects in it cannot be found by SSIS, a warning will be given and the package will continue to run with the saved information.

Adding and removing connections for each environment is not ideal. Rather keep the names environment independent.

HTH

Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

tushar-324804 SSC Veteran Points: 277 More actions · Answer 2

Hi Crispin,

You know what...i read you solution and re-read it. And suddenly i realized something. You're right i was making the mistake of having different connection names. Whereas i could keep only one common name. It solved my problem.

Again when i intentionally kept the old invalid connections, SSIS throws an Error and Warning as well. To simplify, now i have good connections and bad invalid connections in config file.

============================================================

Error: 2007-08-09 11:34:54.67

Code: 0xC001000E

Source: SSIS_TestPckg1

Description: The connection "MyComp.MyDB" is not found. This error is thrown by Connections collection when the specific connection element is not found.

End Error

Warning: 2007-08-09 11:34:54.67

Code: 0x8001F02F

Source: SSIS_TestPckg1

Description: Cannot resolve a package path to an object in the package ".Connections[MyComp.MyDB].Properties[ConnectionString]". Verify that the package path is valid.

End Warning

DTExec: The package execution returned DTSER_SUCCESS (0).

Started: 11:34:53 AM

Finished: 11:34:56 AM

Elapsed: 2.735 seconds

============================================================

I read somewhere...that in an event where the config does not work, the Package loads with its default connections. Does that mean the success of the above package is due to my default connections at design time or the good connections from the config file.

Mean the config connections overrides the default ones. And in an event of failure the Package continues to run on the default conns.

Let me know your perception.

Much appreciate your response.

Thanx,

Tushar

Crispin Proctor SSCoach Points: 18161 More actions · Answer 3

You are correct,

SSIS loads the config file and tries to set the value of properties specified in the file.

If a property path is not found, it cannot set it. The properties themself do not know they should be set by the config file. To them, they already have values and continue to run. Basically, they are totally disconnected.

So yes, your package will run with whatever values they had.

This can bite you as well. Your prod box could be connecting to dev box.

All to do with monitoring

Have a look at using environment variables to hold the path of the config files. A simpler solution.

HTH

Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

tushar-324804 SSC Veteran Points: 277 More actions · Answer 4

Thanx Crispin.

I already have an Environment Variable set which controls the config file.

Thanx for resolving my issue....and for that extra info.

Tushar

Dinesh Asanka SSChampion Points: 11058 More actions · Answer 5

http://www.sql-server-performance.com/articles/dba/package_configuration_2005_p1.aspx

My Blog: http://dineshasanka.spaces.live.com/