HOW TO: Obtain a List of DTS Packages (241249)

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 Explicit
Private 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
    Next
End 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)
    Next
End 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 Explicit
Private 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
    Next
End 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)
    Next
End Sub
				

back to the top