Get DTS Step Description from TSQL?

Question

I am trying to get the DTS Step Name/Description of a given DTS Package in SQL2000. I am not able to see anything in the msdb database. I can see the initial DTS name, however I dont see anything to get the details. Anybody knows where this info is stored?

Answer 1

You can't access the contents of a DTS package directly through SQL server (they are stored as binary in msdb.dbo.sysdtspackages when stored in SQL server, or as binary files in the file system).

However, you can access them via the relevant OLE object - dts.package2.

There are several examples of using the OLE interface to extract DTS step information - this is one of them, which uses another DTS package to run VBScript to extract package information into a file. You could modify this to write data into a table in the database.