April 11, 2008 at 7:27 am
Hello there,
We have around 1300 DTS jobs which are to be migrated to different server. But, we want to find out what database connection they are using and what is the userid used for that connection. e.g.
Job Name Created By Created On DatabaseConnection UserID for Database
ABCD XYZ 01/01/2008 dbSample TestUserID
Does anyone have any idea about how to get these details without opening each DTS job?
Thanks,
Ramesh.
April 17, 2008 at 3:32 pm
This article describes getting a list of DTS jobs, stored in each of the three possibilities of SQL Server, the repository, and in files:
http://support.microsoft.com/kb/241249
I guess in general, there might be several database connections in the same DTS job, and you do have to delve into them on some level to determine what they do. I found this link:
http://www.sqldev.net/dts/SavePkgToFile.htm
The package is persisted as a COM structured storage file. In case of a file it is really a file on disk that can be opened with the COM structured storage file interfaces directly. In case the package is stored inside SQL Server (msdb) the file is serialized into the packagedata column of the sysdtspackages table in the msdb database.
Sounds like a bit or work - can't find any sign that anybody has done this.
How about a bit of lateral thinking / outrageous kludge depending on how you look at it .. if you're connecting via TCP/IP for example, set up the same user IDs on the new server(s), and "fool" the machine running the DTS jobs into running them on the new machines by making entries in the "hosts" file. Possibly consider renaming the machine when then migration is done, although that might cause its own problems. Might not be applicable in your situation, but just a suggestion ...
Tony
April 17, 2008 at 9:18 pm
Thanks for your reply.
Basically problem we have is, we are not sure who all have created all these DTS jobs. Some of the creators might have left the organization and still jobs are running under their ID's (due to nontermination). Hence we want to find out what id's are used for each DTS jobs to connect to SQL server. That is the whole purpose of this exercise.
Thanks,
Ramesh.
April 18, 2008 at 2:23 pm
I'm sure there is a way to find out the connections used by a DTS programatically, but in our case we used DTS documenter to do that.
Regards.
Martin
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply