Microsoft KB Archive/241249

= HOW TO: Obtain a List of DTS Packages =

Article ID: 241249

Article Last Modified on 12/27/2003

-

APPLIES TO


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

-



This article was previously published under Q241249



IN THIS TASK
SUMMARY
 * SQL Server
 * Repository
 * File



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

Keywords: kbhowtomaster kbsqlprog KB241249

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.