Handling DTS Errors in Visual Basic

You need to take the steps described below when handling errors in Data Transformation Services (DTS) applications implemented in Microsoft® Visual Basic®.

DTS applications typically consist of two phases:

In the first phase, the applications create DTS objects, set their properties, and add them to collections of parent objects.
Errors that occur during the object creation/property definition phase can be handled by a typical Visual Basic error handler.
In the second phase, the Execute method of the Package2 object is invoked.
Errors that occur during the Execute will not be propagated back to the caller unless the FailOnError property of the Package2 object is set to TRUE.

When FailOnError is TRUE, the description of the returned error will often tell you only that the package failed because a (named) step failed. To determine why a step failed, the GetExecutionErrorInfo method of the Step object will return the properties of a Visual Basic error object that describe the error.

Troubleshooting Package Execution

To determine the step(s) that raised errors, the ExecutionStatus property of the Step object should have the value DTSStepExecStat_Completed (in enum DTS.DTSStepExecStatus) and the ExecutionResult property should have the value DTSStepExecResult_Failure (in enum DTS.DTSStepExecResult). If FailOnError is TRUE, there will only be one such step. If not, there may be multiple failed steps, depending on the package workflow. The error handler should iterate through all the objects in the Steps collection and not stop when it finds an error.

Error Handler Example

The following code example is a typical error handler that could be used while a package is being developed, and FailOnError is set to TRUE. If failing the package on the first error is undesirable, the sAccumStepErrors function could still be used, but it would need to be called following a normal return from objPackage.Execute, as well as from the error handler.

Private Sub RunDTSPackage( )
    Dim objPackage            As New DTS.Package
    . . .
    On Error GoTo PackageError
    . . .
    objPackage.FailOnError = True
    objPackage.Execute
    Exit Sub
    
PackageError:
    Dim sMsg    As String
    sMsg = "Package failed, error: " & sErrorNumConv(Err.Number) & _
        vbCrLf & Err.Description & vbCrLf & sAccumStepErrors(objPackage)
    MsgBox sMsg, vbExclamation, objPackage.Name
    Exit Function
End Sub

Private Function sAccumStepErrors( _
            ByVal objPackage As DTS.Package) As String
'Accumulate the step error info into the error message.
    Dim oStep       As DTS.Step
    Dim sMessage    As String
    Dim lErrNum     As Long
    Dim sDescr      As String
    Dim sSource     As String
    
    'Look for steps that completed and failed.
    For Each oStep In objPackage.Steps
        If oStep.ExecutionStatus = DTSStepExecStat_Completed Then
            If oStep.ExecutionResult = DTSStepExecResult_Failure Then
            
                'Get the step error information and append it to the message.
                oStep.GetExecutionErrorInfo lErrNum, sSource, sDescr
                sMessage = sMessage & vbCrLf & _
                        "Step " & oStep.Name & " failed, error: " & _
                        sErrorNumConv(lErrNum) & vbCrLf & sDescr & vbCrLf
            End If
        End If
    Next
    sAccumStepErrors = sMessage
End Function

Private Function sErrorNumConv(ByVal lErrNum As Long) As String
'Convert the error number into readable forms, both hexadecimal and decimal for the low-order word.
    
    If lErrNum < 65536 And lErrNum > -65536 Then
        sErrorNumConv = "x" & Hex(lErrNum) & ",  " & CStr(lErrNum)
    Else
        sErrorNumConv = "x" & Hex(lErrNum) & ",  x" & _
                Hex(lErrNum And -65536) & " + " & CStr(lErrNum And 65535)
    End If
End Function

The following code example is the message generated by the above handler when a package with a connection that references a non-existent database is run: