Microsoft KB Archive/240221

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

Article ID: 240221

Article Last Modified on 12/20/2005

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q240221



IN THIS TASK
SUMMARY
 * Handle Errors in Data Transformation Services Package and Step Objects

REFERENCES



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

