Bandwidth Monitor
SQLDTS.comSQLIS.comSQLIS Wiki
Execute a package from Visual Basic (VB)
By Darren Green
Page 1 | Page 2 | Page 3
Private Sub ExecutePackage()

    Dim oPKG As DTS.Package, oStep As DTS.Step
    Set oPKG = New DTS.Package
    
    Dim sServer As String, sUsername As String, sPassword As String
    Dim sPackageName As String, sMessage As String
    Dim lErr As Long, sSource As String, sDesc As String
    
    ' Set Parameter Values
    sServer = "MyServer"
    sUsername = "MyUser"
    sPassword = "MyPassword"
    sPackageName = "MyPackage"
    
    ' Load Package
    oPKG.LoadFromSQLServer sServer, sUsername, sPassword, _
        DTSSQLStgFlag_Default, , , , sPackageName
        
    ' Set Exec on Main Thread
    For Each oStep In oPKG.Steps
        oStep.ExecuteInMainThread = True
    Next
    
    ' Execute
    oPKG.Execute
    
    ' Get Status and Error Message
    For Each oStep In oPKG.Steps
        If oStep.ExecutionResult = DTSStepExecResult_Failure Then
            oStep.GetExecutionErrorInfo lErr, sSource, sDesc
            sMessage = sMessage & "Step """ & oStep.Name & _
                """ Failed" & vbCrLf & _
                vbTab & "Error: " & lErr & vbCrLf & _
                vbTab & "Source: " & sSource & vbCrLf & _
                vbTab & "Description: " & sDesc & vbCrLf & vbCrLf
        Else
            sMessage = sMessage & "Step """ & oStep.Name & _
                """ Succeeded" & vbCrLf & vbCrLf
        End If
    Next
    
    oPKG.UnInitialize
    
    Set oStep = Nothing
    Set oPKG = Nothing
    
    ' Display Results
    MsgBox sMessage
    
End Sub
Page 1 | Page 2 | Page 3