mass change connection settings for all DTS pkgs?

[MColeman]

My dba wants to know if there is a way to mass update all DTS packages and change the server name, username, and
password without opening each one in designer.

 

[mrdenny]

I do not think that there is. The DTS package is stored in the msdb database in a binary data field.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[MColeman]

Thanks for the info.

I've been looking high and low! So far, I haven't come up with anything.

I was sure someone had found a way to use sp_OASetProperty
to change the connection objects properties. Then I could use a cursor to spin thru sysdtspackages to get the dts name.

I did come across a DTS compare tool by RedGate we could use to compare the packages between the two servers after we migrate to make sure all of the changes were made.

I'm going to keep looking.

 

[MColeman]

We found some freeware on code project. We downloaded it, and it works for mass updating the connection settings in DTS packages.

http://www.codeproject.com/Purgatory/DTS_Export.asp

 

[MDXer]

One alternative is to use udl files as the data sources. Then when you need to globaly change a datasource it is a single edit.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck