You are currently offline, waiting for your internet to reconnect

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

This article was previously published under Q240221
This article has been archived. It is offered "as is" and will no longer be updated.
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
REFERENCES
For additional information about how to handle DTS error events, click the article number below to view the article in the Microsoft Knowledge Base:
221193 HOW TO: Install Data Transformation Services (DTS) Event Handlers in Visual Basic
back to the top
Properties

Article ID: 240221 - Last Review: 12/05/2015 15:38:44 - Revision: 3.5

  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 7.0 Standard Edition
  • kbnosurvey kbarchive kbarttypeinf kbhowtomaster kbinfo KB240221
Feedback