none
Trying to find an old DTS package? RRS feed

  • Question

  • I've inherited a SQL2005 server (that was upgraded from 2000 at some point.)   There is a SQL Agent job that attempts to do a DTSRun command, but it fails with the "SQL Server does not exist or access denied" message.  I tried running the same command, but editing the "GUID identifier" (or whatever that long string is supposed to be called in the DTSRun command) so that it's something that wouldn't exist - and I get a different error.  I'm guessing that means the DTS package must be around somewhere.

    However, when I go into Integration services -> stored packages -> MSDB, I see a couple DTS packages, but not the one that I'm looking for.

    Is there any way to use that GUID (whatever) to track down the package so that I can edit it?  Or any other way to find it?

    Thanks in advance for any ideas.

    Matt
    Wednesday, April 22, 2009 1:37 PM

Answers

  • OK - because I had that backup, I was able to get things fixed.

    1) Take the SQL2000 msdb copy, and restore it to a new database name on the SQL2005 server.
    2) run the following (which will copy all of the old packages to DTS2000 legacy packages on SQL2005: 

    insert

     

    into sysdtspackages

    select

     

    * from oldmsdb..sysdtspackages 

    3) You should now be able to see the old packages in SQL Management Studio, <server>, Managment, Legacy, Data Transformation Services.   Right-click on whatever you want there and you should be able to edit it, or migrate it to SSIS.

    Thanks the help - getting me started on that table helped lead to everything else.

    • Marked as answer by Tony Tang_YJ Wednesday, April 29, 2009 10:27 AM
    Wednesday, April 22, 2009 8:38 PM

All replies

  • SELECT * FROM msdb..sysdtspackages90

    The package's guid ought to be in the id field on that table.  If you aren't seeing it in IS but it shows in that table, it could be stored into a subfolder (sysdtspackagefolders90)  It could be on the file system too, so check the actual parameters for the dtexec call.

    Wednesday, April 22, 2009 4:13 PM
    Answerer
  • Thanks.

    I can't find the ID there (and looking at the name fields in that table, I can tell what I'm looking for isn't in either of them.)

    I also looked in the registry (just for kicks) for the id, and it doesn't exist anywhere there.

    The dtsrun command is just DTSRun /~Z0xF1E10EDA532119DC8D7B978D2CF285631B9AA5F839B91CC6CA6B95428EF249D4F9A7953FEA631D01857468495780C51B67B31F7602611F879C35E3FD0C180C6C2EE0A7A2F04EA08216D27F36786297921FA2FDAE77EB46DA6D229636A88EC981E39445205113B346DBCA5B87443F1F94789FF13903EB42FD00B26F8   so I can't get much out of there.

    Hmm - I just found a backup of the 2000 server's msdb database.  If I restore that to a new DB name, I could certainly query it to see if I find the package in there.  If I could - how could I get it out of that database?

    thanks again
    Wednesday, April 22, 2009 7:25 PM
  • OK - because I had that backup, I was able to get things fixed.

    1) Take the SQL2000 msdb copy, and restore it to a new database name on the SQL2005 server.
    2) run the following (which will copy all of the old packages to DTS2000 legacy packages on SQL2005: 

    insert

     

    into sysdtspackages

    select

     

    * from oldmsdb..sysdtspackages 

    3) You should now be able to see the old packages in SQL Management Studio, <server>, Managment, Legacy, Data Transformation Services.   Right-click on whatever you want there and you should be able to edit it, or migrate it to SSIS.

    Thanks the help - getting me started on that table helped lead to everything else.

    • Marked as answer by Tony Tang_YJ Wednesday, April 29, 2009 10:27 AM
    Wednesday, April 22, 2009 8:38 PM