DTS How to… Obtain a List of DTS Packages

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:mssql7devtoolssamplessqlnsvbbrowse


There is also a relevant Microsoft Knowledge Base Article:

  • Article ID: Q241249
  • INF: How to Obtain a List of DTS Packages
  • http://support.microsoft.com/support/kb/articles/Q241/2/49.asp

  • Get the Free Newsletter!

    Subscribe to Cloud Insider for top news, trends & analysis

    Latest Articles