DTS package is lost

Question

DTS package is lost

hirenk

SSCrazy

Points: 2000
More actions
November 1, 2002 at 8:14 am

#158860

We have a DTS package on one server, used for a scheduled job. When I am trying to open this package, it gives me error 'specified file not found.' I see a folder with the same name as package in one of the machines where it was created. IN this folder bunch of files with *_exp name. But I don't know how to specify this path to package or infact save package somewhere else (like on server msdb DB) not on this machine. BOL says
If you receive the message “Cannot find the specified file,” when attempting to access a DTS package saved to a COM-structured storage file, verify that:
The directory path is specified correctly.
Permissions are set correctly.
Doesn't say how to do this. Because I just can't open/ edit this package.
Thanks.

Viewing 5 posts - 1 through 4 (of 4 total)

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

zach_john SSCertifiable Points: 5922 More actions · Answer 1

Not sure if this will help but you can move a DTS package outside of the GUI by modifying the code you'll find here to only select the desire package.

http://www.sqlservercentral.com/columnists/awarren/copyingdtspackagestoadifferentserver.asp

One thing I have learned in ver SQL server 7.0 is that your SQL Client should be at the same version as your server IE if you have installed sp3 on the server you should then have installed sp3 on your client desktop. See http://www.databasejournal.com/features/mssql/article.php/1461391#3 in regards to this.

Hope this helps,

Zach

John Zacharkan

hirenk SSCrazy Points: 2000 More actions · Answer 2

Thanks Zack. Atleast now I know what the problem is. IT is version issue. Package was written from client machine (2000). Server that package is running on is 7.00. I checked Andy's script , looks good.

But 2 things,

- I don't know how to modify this for my package.

- I am not sure where package is actually stored. I am looking into msdb DB sysdtspackage has entry for this package, but again in client machine it seems like package is there.

Thanks for your help.

hirenk SSCrazy Points: 2000 More actions · Answer 3

I found other solution for my problem from databasejournal.com

textcopy /S ServerName /U sa /P /D msdb /T sysdtspackages /C packagedata /W "where name='DTSName'" /F DTSText.dts /O

This command will bring package out as dts file. Then it can be opened from EM/open package.

Thanks.

zach_john SSCertifiable Points: 5922 More actions · Answer 4

Very coool, I'll have to add that to my bag of tricks.

Good Luck

Zach

John Zacharkan