INF: How to Obtain a List of DTS Packages

ID: Q241249

The information in this article applies to:

Microsoft SQL Server version 7.0

SUMMARY

The Data Transformation Services (DTS) Package can be stored in one of three places. The following code samples describe the three methods to obtain a list of packages for each storage method.

SQL Server

Packages stored on SQL Server are saved in the msdb database. Execute the following query to obtain the list:


exec msdb..sp_enum_dtspackages

Repository

For packages stored in the Repository you must enumerate the Repository through the Repository object. This must first be registered 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

File

For packages stored in a file, you can obtain a list of separate saved packages within 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

Additional query words:

Keywords : kbSQL
Version : winnt:7.0
Platform : winnt
Issue type : kbinfo