Where DTS connection and db name stored ?

Question

Post reply

Where DTS connection and db name stored ?

Jimmy Jen

SSCrazy

Points: 2915
More actions
August 23, 2007 at 8:26 am

#197371

Can someone tell me where DTS's connection and database name is stored in SQL 2000 ? I'm asking this because we are making some table changes in database A on server X. But I don't know what DTS on server Y are pointing to server X and database A. If dts connection and db name are stored in system tables, then I can just write a SELECT statement and find out what dts will be affected.
Thanks in advance...Jimmy

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

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

Antares686 SSC Guru Points: 125444 More actions · Answer 1

You have to open each packag to see, the only stored in the system is an encrypted version of the package binary.

Jimmy Jen SSCrazy Points: 2915 More actions · Answer 2

I have lots of DTS on each server and it's impractical to open one by one. Have anybody use ActiveX Script to pull out those info from DTS Package Object ? Any sample ?

Sam Greene SSCertifiable Points: 5252 More actions · Answer 3

This won't help you now, but you could have stored connection info in an .ini file and used the dynamic property task to set these values at runtime.

Kenneth.Fisher SSC-Insane Points: 20396 More actions · Answer 4

Unfortunatly I can't give you the script myself because I wrote it for my current job and don't want to get in trouble

That being said I can point you in the right direction.

You can get a list of packages from master..sysdtspackages. Then use sp_OACreate 'DTS.Package' to get a DTS package object. Then use sp_OAMethod to run the 'LoadFromSQLServer' method on it. Then check the Connections collection and scan through it looking at properties like .. Name, DataSource, Catalog etc.

Here is the path I was given to get me started

http://www.dbazine.com/sql/sql-articles/larsen8

And here is the path to where I asked the same question

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=19&messageid=391058

Kenneth Fisher

Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

Jimmy Jen SSCrazy Points: 2915 More actions · Answer 5

No problem. I looked at the link you provided and I understood. Thanks a lot.

Just to clarify: DataSource = ServerName and Catalog = DatabaseName ?

Kenneth.Fisher SSC-Insane Points: 20396 More actions · Answer 6

To the best of my knowledge usually. The datasource can also be a file path and I have seen it be an IP address before as well.

Ken

Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]