Hi!

As I have 15 DTS Packages and the same connection in each, i'd like to
find a process to change the password of the connections in one time.
(rq : I'm using SQL Serveur authentification for a Microsoft OLE DB
Connection)
I don't want to save the password not masked, so it seems i can't use
Dynamic Properties Task.
Is there a way to do that?

In article <ad817fad.0308180630.6a04c9bb (AT) posting (DOT) google.com>, Ccile
<cecile.challende (AT) m6net (DOT) fr> writes

You could write some code to load you package, enumerate connections ,
and change the password depending on the server/user id combination,
then save the package again.

The "Transfer Packages Sample Application" would be a start for some
code-
Tools & Tasks
http://www.sqldts.com/default.aspx?220,272,272,1,1


Using the object model does mean that you would loose any custom layout
or annotations however.


Using Dynamic Properties doesn't mean the password has to be saved
un-encrypted in the package, as you could pass the password it at
run-time. Obviously if going through a global variable you would have to
blank that variable out before you save the package in the designer. The
issue here is storing them safely to pass in at run-time. The obvious
way to pass in at run-time assumes you execute the package via DTSRUN in
which case you can use the /A parameter to pass in a global variable for
use by the Dynamic Properties task. If you use a custom execute method
then you could set the connection directly.

Another alternative is to encapsulate the "connection enumeration,
changing passwords depending on server/user id combination" logic and
wrap this in a custom task. The custom task could read from an encrypted
store on disk or the registry for example. This is actually what I do.
Not sure it is the best method, and I sometimes worry about my
implementation from a security perspective, but that's another problem.

The best method would be to use integrated security connections, but I
know that is not always possible. Another approach is to use very low
privilege accounts perhaps offsetting the weakness of the storage
mechanism you chose.

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com