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 */
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:

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

  • Latest Articles