How To Get the PackageID for Use in the LoadFromRepository Function

This article was previously published under Q257868
This article has been archived. It is offered "as is" and will no longer be updated.
In order to load a specific DTS package that is stored in the repository you must use the LoadFromRepository function. One of the function's required parameters is the PackageID. This ID identifies the DTS Package uniquely in the repository. The code sample below demonstrates how to retrieve this PackageID and use it in the function.
The following code loops through all DTS packages stored in the repository until the specified package name is found. Once the package that you want to load is found you can then get the correct PackageID and use it in the LoadFromRepository function. This code then loads the package and executes it.

To run the following code, paste it into a new form's general declarations section of a Standard EXE project and make sure to register the Microsoft DTSPackage Object Library and Microsoft Repository object references for the project:
Dim goPackage As DTS.PackagePrivate Sub Form_Click()Set goPackage = New DTS.PackageDim oRep As New Repository       Dim oITF As InterfaceDef       Dim oCol As ObjectCol       Dim oObj As RepositoryObject       Dim sPackageID As String       Dim strPkgName As String              strPkgName = "mytest" 'name of package to load       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            If oObj.Name = strPkgName Then  'look for specific package            sPackageID = oObj("IDtsTransformationPackage").PackageID 'get PackageID            Exit For            End If       NextOn Error GoTo LoadErrgoPackage.LoadFromRepository "(local)", "MSDB", "sa", "", sPackageIDgoPackage.ExecuteExit SubLoadErr:        FAIL "Unable to load package " & strPkgName & ". Error: " & Error$End SubSub FAIL(strMessage$)    MsgBox strMessage    EndEnd Sub				
For additional information on how to retrieve packages in all types of stores, click the article number below to view the article in the Microsoft Knowledge Base:
241249 INF: How to Obtain a List of DTS Packages
For additional information on DTS and the Microsoft Repository, click the article number below to view the article in the Microsoft Knowledge Base:
246333 INF: SQL Server DTS and the Microsoft Repository

Article ID: 257868 - Last Review: 10/20/2013 18:30:03 - Revision: 1.2

Microsoft SQL Server 7.0 Standard Edition

  • kbnosurvey kbarchive kbhowto KB257868