INF: How To Handle Errors in DTS Package and Step Objects

ID: Q240221

The information in this article applies to:

Microsoft SQL Server version 7.0

SUMMARY

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

MORE INFORMATION

SQL Server 7.0 CD has a Visual Basic sample in devtools\Samples\DTS\dtsexmp3 folder with an example of error handling (given in the following) for a Step.


   '******************************************************************
   '  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

However, the earlier code has a bug where calling GetExecutionErrorInfo (lpErrorCode) method does not populate lpErrorCode with error code information. It should not enclose the parameter in parenthesis since it is called by Reference. The following call will populate the error code correctly:


   oPackage.Steps(i).GetExecutionErrorInfo lpErrorCode

A better way to retrieve error information is to include additional (optional) parameters to GetExecutionErrorInfocall, as given in the following:


    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 Package object failure. Therefore, error information for each step is unavailable from the COM IErrorInfo object or the Visual Basic Err object.

GetExecutionErrorInfo method does not return detailed error description in SQL Server 7.0. In order to get additional error information, you should also implement event handlers in your code and check for error description inside 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

For additional information about how to handle DTS error events, please click the article number below to view the article in the Microsoft Knowledge Base:

Q221193 INF: How To Install DTS Event Handlers In Visual Basic

You should also check Visual Basic Err object for DTS package 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

Note that some error details may not be available. In addition, using SQL Enterprise Manager UI, the following options on DTS Package properties, General tab let you control the error handling:

Error file
Specify the file to which package run-time errors are logged (the DTS Package.LogFileName property). This can be in UNC format. (...) Locate and specify the log file.

Fail package on first error
Specify whether package execution terminates if the first step fails.

Write completion status to event log
Specify whether to write the package execution status to the Windows NT application log. This option is available only on computers running 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 that when a DTS package step fails, the Error file and Windows NT Event log do not provide detailed information on why it has failed, even though DTS Wizard and Designer UI displays the full error in a message box.

Additional query words:

Keywords : kbSQLServ700 kbDSupport kbinfo
Version : winnt:7.0
Platform : winnt
Issue type : kbinfo