How To Get the PackageID for Use in the LoadFromRepository Function (257868)

MORE INFORMATION

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.Package
Private Sub Form_Click()
Set goPackage = New DTS.Package

Dim 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
       Next
On Error GoTo LoadErr
goPackage.LoadFromRepository "(local)", "MSDB", "sa", "", sPackageID
goPackage.Execute

Exit Sub
LoadErr:
        FAIL "Unable to load package " & strPkgName & ". Error: " & Error$
End Sub

Sub FAIL(strMessage$)
    MsgBox strMessage
    End
End Sub