How to find the dts connection information (using query)
-
I have over 200 DTS packages in one of my servers (SQL 2K Std SP4). This server is dedicated to be used for DTS packages. The dts packages pull data from several sources to several destination:
Text file to SQL Server (to and from)
Informix to Oracle (to and from)
Sql Server to Oracle (to and from)
Informix to SQL Server (to and from)
Excel to Oracle (to and from)
There are several more source and destination. I guess you get the picture. Here is my question:
I need to find out all DTS package from Server A to Server B. In this case, both server A and Server B are Sql Servers. How can I find it out programatically using some queries. Opening every DTS package in design mode and finding out is out of question.
Is there any way to get this info from system catalog (may be from msdb..SysDTSPackages, sysdtscategories etc). Looking forward for your response. Thanks.
Give me a fish, you feed me for a day! Teach me to fish, you feed me for a life time.
-
Somebody please reply. Waiting for your reply.
Give me a fish, you feed me for a day! Teach me to fish, you feed me for a life time.
-
There is no simple way that I know of to do this.
The data stored in the packagedata column is encrypted and therefore can not be used.
Not very useful, I know, but I've got my lateral thinking head on (it only comes out on Friday when work's quiet!!)
-
I'm with Adrian. There isn't an easy way to do that. Here are a thread and a script that I've referenced before that might help:
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=308151
http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=1613
Greg
Greg
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply