Connection Manager passwords
-
This has probably been covered but, I can't seem to find the info in my searches.
I have a connection manager to connect to a DB2 database in my package. Unfortunately, my company has never seen fit to provide an automation account so that I don't have to deal with expired passwords killing an automated data pickup.
I have been trying to figure out how to set the login/password of a connection manager at runtime? I see the properties when I am editing the connmgr but, the properties are not exposed in the variables window. Does that mean they are not available or that they need to be addressed in a special way?
What I would ultimately like to do is look up the login/password pair in a SQL Server table and set the connection manager accordingly so that it doesn't get locked out when I have to change a password on the DB2 account (changing passwords in a dozen packages each change doesn't appeal to me).
If there is already a white paper on this all I need is the link.
Thanks
Gerald
-
Enter configuration files:
Why do you expect the package properties to be exposed in the variables pane? it's a variables pane. There is also a properties pane.
You can use SSIS configurations to set the value of everything in the package. It will load up the values from the config files (or SQL table) and set them before it starts running.
You can also set the value of a property through expressions on the particular task / component / package using a ... variable...
Have a look for SSIS config files and methods.
If you have a dozen packages running, connecting to external sources, and you have not used config files, your life will be rather unappealing when the source server name changes..
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!
-
Ah hell, Crispin, I don't expect the package properties to be in the variables or anywhere in particular, I just want to get at them at runtime so I can stuff them with the latest greatest password when my package starts. It sounds like config files would be a good way to go. I guess I better bone up on config files and learn how to do a centralized one so that all my loaders that hit this DB2 account can use the same one. I am still looking and can't find where the Password property is exposed in the Expressions, so I guess that means configs are the only way to do this?
Your idea made me think... can I set up a base config with the info needed to access our main data sources and place it so that each individual package configs (possibly in multiple projects ) can overload it to add more specific info. In otherwords start with a department config, and add the Crispin or Gerald config info on top of it?
I'm still a rookie at SSIS so you can't shoot me till next year
Gerald
-
You're now on the right track. Here's how I use configs.
Connection managers are named according to some convention. _CON_SQL_ABC where:
_ - indicates the managed is controlled from an external source. i.e. config file.
CON - Connection Manager
SQL - SQL connection
ABC - Some discriptive name.
it also follows: _CON_FTP_MySite
if all your packages have the same manager and same connection requirements, you need only one config file to configure all of them.
You will have one config file per connection.
Finally, you have a config which is package specific and cannot be used by other packages.
A configs are specified in a order and set the values top down. So if file 1 sets abc = 123, file two will overwrite it.
All your files live in a central repository with the expection of the package specific one which lives alongside the package.
You set a environment variable (OS thing) to hold the path to each config file. In SSIS, you load the file from the environment variable.
Make more sense now?
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!
-
You can also set connection information at runtime through expressions. If you need something really dynamic - say passing in the username and password through a user interface - package configurations may not be the best way.
If you just need to regularly change a password for a package that runs on a schedule, package configurations work pretty well.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply