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 |
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 |
The information in this article applies to:
- Microsoft SQL Server 7.0 (Version: 7.0)
- Microsoft SQL Server 2000 (all editions)
© 2003 Microsoft