HOW TO: Obtain a List of DTS Packages

Article translations Article translations
Article ID: 241249 - View products that this article applies to.
Expand all | Collapse all

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.

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
				

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
				

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
				

Properties

Article ID: 241249 - Last Review: June 22, 2014 - Revision: 5.0
Applies to
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 2000 64-bit Edition
Keywords: 
kbhowtomaster kbsqlprog KB241249

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com