2 Answers
2

To open a package that's stored on the local SQL Server to examine what it does you would open it with either:

a) SQL Enterprise Manager (SQL 2000) - packages should be listed under the Data Transformation Services node under Local Packages.

b) In SQL 2005/2008 I think you can work with legacy DTS packages using Management Studio or the BI Development Studio. Packages should be listed under Management\Legacy\Data Transformation Services. I've seen mention of it sometimes generating errors on 2008 for which there's a fix available from MS.

It sounds like the job is calling a DTS package to do the work. The name of the package in a DTSRUN command line is given after the /~N command switch. The server that the package is stored on is given after the /~S command switch.

Are there DTS packages stored on your server? If so, look for one that has the same name as the job.

As I said, the name of the DTS package is encrypted. After much Googling, I found a way to read it and it was just the name of the package with a the user and the password. Where are the DTS packages stored?
–
SaiyineDec 21 '10 at 11:35

DTS packages are stored in the msdb database, which is a system database. If you schedule a DTS package from Enterprise Manager (EM) as a SQL job it will show the encrypted name in the job step, not sure if that helps you to find the name. You can extract a DTS package into a structured file from EM as well.
–
Jason CumberlandNov 19 '13 at 19:35