HOW TO: Obtain a List of DTS Packages

This article has been archived. It is offered "as is" and will no longer be updated.
Summary
You can store the Data Transformation Services (DTS) package in one of three places. The following code samples describe the three methods to obtain a list of packages for each storage method.

back to the top

SQL Server

Packages that you store on SQL Server are saved in the msdb database. Run the following query to obtain the list:
exec msdb..sp_enum_dtspackages				
back to the top

Repository

For packages that you store in the Repository, you must enumerate the Repository through the Repository object. You must first register this by adding a Reference to the Microsoft Repository object. Use the following code fragment to obtain the list of packages:
Option ExplicitPrivate Sub EnumPackages_Click()    Dim oRep As New Repository    Dim oITF As InterfaceDef    Dim oCol As ObjectCol    Dim oObj As RepositoryObject    oRep.Open "Driver={SQL Server};Server=(local);Trusted_Connection=yes;Database=msdb"        ' OBJID of IDtsTransformationPackage interface    Set oITF = oRep.Object("{{EBB9995C-BA15-11d1-901B-0000F87A3B33},000032CA}")    Set oCol = oITF.ObjectInstances    For Each oObj In oCol        Debug.Print oObj.Name    NextEnd Sub				
InMicrosoft Visual Basic .NET, the code requires some small changes:
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click    Dim oRep As New RepositoryTypeLib.Repository    Dim oITF As RepositoryTypeLib.InterfaceDef    Dim oCol As RepositoryTypeLib.ObjectCol    Dim oObj As RepositoryTypeLib.RepositoryObject    oRep.Open "Driver={SQL Server};Server=(local);Trusted_Connection=yes;Database=msdb"     ' OBJID of IDtsTransformationPackage interface    oITF = oRep.Object("{{EBB9995C-BA15-11d1-901B-0000F87A3B33},000032CA}")    oCol = oITF.ObjectInstances    For Each oObj In oCol        Debug.WriteLine(oObj.Name)    NextEnd Sub				
back to the top

File

For packages that are stored in a file, you can obtain a list of separate, saved packages in the file by using the following code fragment:
Option ExplicitPrivate Sub Command1_Click()    Dim oPackage As New DTS.Package    Dim oInfoCollection As DTS.SavedPackageInfos    Dim oInfo As DTS.SavedPackageInfo    Set oInfoCollection = oPackage.GetSavedPackageInfos("c:\temp\samples.dts")        For Each oInfo In oInfoCollection        Debug.Print oInfo.PackageName + " " + oInfo.VersionID    NextEnd Sub				

In Visual Basic .NET, you have to add a reference to Microsoft DTSPackage Object Library first.Use the following code:
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click    Dim oPackage As New DTS.Package    Dim oInfoCollection As DTS.SavedPackageInfos    Dim oInfo As DTS.SavedPackageInfo    oInfoCollection = oPackage.GetSavedPackageInfos("c:\temp\samples.dts")    For Each oInfo In oInfoCollection        Debug.WriteLine(oInfo.PackageName + " " + oInfo.VersionID)    NextEnd Sub				
back to the top
Properties

Article ID: 241249 - Last Review: 12/05/2015 15:48:30 - Revision: 5.0

Microsoft SQL Server 7.0 Standard Edition, Microsoft SQL Server 2000 Standard Edition, Microsoft SQL Server 2000 64-bit Edition

  • kbnosurvey kbarchive kbhowtomaster kbsqlprog KB241249
Feedback