Microsoft KB Archive/240406

{|
 * width="100%"|

-

The information in this article applies to:


 * Microsoft SQL Server version 7.0
 * Microsoft Visual Basic Professional and Enterprise Editions for Windows, version 6.0

-

SYMPTOMS
When a Data Transformation Service (DTS) Package step fails, the OnErrorEvent, the GetExecutionErrorInfo method call, and the Visual Basic Error object might not provide all the details on why it failed, even though the DTS Wizard and the Designer UI display the full error in a message box.

STATUS
This has been confirmed to be a problem with DTS object model in SQL Server 7.0.

MORE INFORMATION
When a DTS package step fails, the Error file and NT Event log do not provide detailed information on why it failed, even though the DTS Wizard and the Designer UI display the full error in a message box.

When a DTS package step fails, you should handle all errors in DTS Package and Step objects by using GetExecutionErrorInfo method call, OnError event, and VB Err object, as described in the following Microsoft Knowledge Base article:

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

Steps to Reproduce Problem
 Create a table with a primary key in SQL Server pubs database and import from a text file with a duplicate key: The last insert statement produces the error: Create a text file TextData.txt with the following data: Create a DTS package using the DTS Import Wizard to copy data from the text file created above to SQL Server pubs database table DTS_Text using SQLOLEDB provider. Save the package in SQL Server as Text_Import. When you run the package, a detailed error message about primary key violation is displayed. Create a new Visual Basic .exe project and add a reference to Microsoft DTSPackage Object Library ( DTSPkg.rll ). Add a command button and paste the following code in the project:  When you execute the program, it opens and executes the package. You should see the following error message in the debugger's immediate window. Note that it does not include the error message about primary key violation:

goPackage_OnStart Fired

goPackage_OnError Fired

DTS Data Pump

-2147467259

Error at Destination for Row number 4. Errors encountered so far in this task: 1.

goPackage_OnError Fired

Microsoft OLE DB Provider for SQL Server

-2147217887

The statement has been terminated.

goPackage_OnFinish Fired

goPackage_OnQueryCancel Fired

-2147217887 Microsoft OLE DB Provider for SQL Server

The statement has been terminated.

