DTS How to... Obtain a List of DTS Packages

November 28, 1999

1 How to Obtain a List of DTS Packages

A common request is how to obtain a list of DTS Packages, to populate a list box for example. The easiest way is to just query the system tables of the msdb database:

/* List Server Packages */
SELECT DISTINCT name FROM sysdtspackages

/* List Repository Packages */
SELECT DISTINCT Name, PackageID 
FROM TfmPackage INNER JOIN RTblNamedObj 
 ON tfmpackage.IntID = RTblNamedObj.IntID

N.B. The PackageID is very important for repository packages, as it is required to identify and load the package. Server packages can be loaded just using the name.

An alternative is to use SQL-NS. For an example of this see the sample:
  x:\mssql7\devtools\samples\sqlns\vb\browse\

There is also a relevant Microsoft Knowledge Base Article: