Select the product you need help with
- Internet Explorer
- Windows Phone
- More products
HOW TO: Handle Errors in Data Transformation Services "Package" and "Step" Objects
Article ID: 240221 - View products that this article applies to.
This article was previously published under Q240221
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.
Handle Errors in Data Transformation Services Package and Step ObjectsThere 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:
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:
Or, you can use the following code sample to retrieve error information by including additional (optional) parameters to the GetExecutionErrorInfo call:
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:
Additionally, check Visual Basic Err object for DTS package object errors. For example:
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:
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.
Constant Value Description DTSErrorMode_Continue 1 Log exceptions and continue DTSErrorMode_FailPackage 3 Fail package execution DTSErrorMode_FailStep 2 Fail step execution
For additional information about how to handle DTS error events, click the article number below to view the article in the Microsoft Knowledge Base:
(http://support.microsoft.com/kb/221193/EN-US/ )HOW TO: Install Data Transformation Services (DTS) Event Handlers in Visual Basic