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
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
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
Keywords: kbhowtomaster kbsqlprog KB241249