HOW TO: Handle Errors in Data Transformation Services "Package" and "Step" Objects (240221)

SUMMARY

This article describes how to handle errors in the Data Transformation Services (DTS) Package and Step objects by using the GetExecutionErrorInfo method call, OnError event, and the Microsoft Visual Basic Err object.

back to the top

Handle Errors in Data Transformation Services Package and Step Objects

There is Visual Basic sample in the Devtools\Samples\DTS\Dtsexmp3 folder in the SQL Server 7.0 CD. This sample includes the following example of error handling for a Step object:

   '******************************************************************
   '  Package Error Handler
   '******************************************************************
   PackageError:
   For i = 1 To oPackage.Steps.Count
        If oPackage.Steps(i).ExecutionResult = DTSStepExecResult_Failure    Then
              oPackage.Steps(i).GetExecutionErrorInfo (lpErrorCode)
               iStatus = False
               With frmSQLData.StatusBar1
                   .SimpleText = oPackage.Steps(i).Name + " in the " +    oPackage.Description + " failed."  ' Update the status bar
               End With
           End If
   Next i
   If iStatus = True Then
       With frmSQLData.StatusBar1
              .SimpleText = oPackage.Description + " Successful"   ' Update the status bar
       End With
   End If
				

The earlier code creates a problem where calling the GetExecutionErrorInfo (lpErrorCode) method does not populate lpErrorCode with error code information. The problem occurs because the code enclose the parameter in parenthesis, which is not required because the parameter is called by the reference.

To correctly populate the error code, use the following call:

   oPackage.Steps(i).GetExecutionErrorInfo lpErrorCode
				

Or, you can use the following code sample to retrieve error information by including additional (optional) parameters to the GetExecutionErrorInfo call:

    iStatus = True
    lpErrorCode = -1
    Dim ErrSource As String
    Dim ErrDescription As String
    
    For i = 1 To oPackage.Steps.Count
        If oPackage.Steps(i).ExecutionResult = DTSStepExecResult_Failure Then
            With oPackage.Steps(i)
              .GetExecutionErrorInfo lpErrorCode, ErrSource, ErrDescription 
              Debug.Print lpErrorCode
              Debug.Print ErrSource
              Debug.Print ErrDescription             End With
            iStatus = False
            With frmSQLData.StatusBar1
                .SimpleText = oPackage.Steps(i).Name + " in the " + oPackage.Description + " failed."  ' Update the status bar
            End With
        End If
    Next i
				

Step object failure is separate from DTS Package object failure. Therefore, error information for each step is unavailable from the COM IErrorInfo object or the Visual Basic Err object.

The GetExecutionErrorInfo method does not return a detailed error description in SQL Server 7.0. To obtain additional error information, you must also implement event handlers in your code and check for error description inside the OnError event. For example:

   Private Sub goPackage_OnError(ByVal EventSource As String, _
                                 ByVal ErrorCode As Long, _
                                 ByVal Source As String, _
                                 ByVal Description As String, _
                                 ByVal HelpFile As String, _
                                 ByVal HelpContext As Long, _
                                 ByVal IDofInterfaceWithError As String, _
                                 ByRef pbCancel As Boolean)
                                   
      Debug.Print "goPackage_OnError Fired"     
      Debug.Print Source
      Debug.Print ErrorCode
      Debug.Print Description
   End Sub
				

Additionally, check Visual Basic Err object for DTS package object errors. For example:

   '******************************************************************
   '  Package Error Handler
   '******************************************************************
   Error_Handler:
   Dim Msg As String
   If Err.Number <> 0 Then
      Msg = "Error # " & Str(Err.Number) & " was generated by " _
            & Err.Source & Chr(13) & Err.Description
      MsgBox Msg, , "Error", Err.HelpFile, Err.HelpContext
      Debug.Print Msg
   End If
				

However, some error details may not be available.

In SQL Server Enterprise Manager, you can control the error handling by using the following options. To access these options, open the properties of the DTS package, and then click the Logging tab:

• Error file: Use this option to specify the file to which package run-time errors are logged (the DTS Package.LogFileName property). This can be in UNC format. Click the ellipsis button (...) to locate and to specify the log file.
• Fail package on first error: Use this option to specify whether package execution quits if the first step fails.
• Write completion status to event log: Use this option to specify whether to write the package execution status to the Windows NT Application log. This option is only available on computers that are running Microsoft Windows NT.
  
  Using the object model DTSErrorMode (package constant) specifies error modes for DTS package execution.

                          Constant Value        Description 

DTSErrorMode_Continue           1 		Log exceptions and continue 

DTSErrorMode_FailPackage        3 		Fail package execution 

DTSErrorMode_FailStep           2 		Fail step execution 
				

NOTE: When a DTS package fails, the Error file and the Windows NT Application event log do not provide detailed information about why it has failed even though the DTS Wizard and Designer user interface displays the full error in a message box.

back to the top