Microsoft KB Archive/251229

= FIX: GetExecutionErrorInfo and OnError Event May Not Return All Errors =

Article ID: 251229

Article Last Modified on 3/14/2006

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q251229



BUG #: 56362 (SQLBUG_70)

BUG #: 56363 (SQLBUG_70)



SYMPTOMS
When a Data Transformation Service (DTS) Package step fails, the OnError event and the GetExecutionErrorInfo method may not provide all the error messages, even though the DTS Wizard, DTSRun tool, and the Designer UI report the full error detail.



STATUS
Microsoft has confirmed this to be a problem in SQL Server 7.0. This problem has been corrected in U.S. Service Pack 2 for Microsoft SQL Server 7.0. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

254561 INF: How to Obtain Service Pack 2 for Microsoft SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0

For more information, contact your primary support provider.



MORE INFORMATION
When a DTS package step fails, the Error file and the Windows NT Event log do not provide detailed information on why it failed. Therefore, it is important to programmatically keep track of DTS errors at various execution steps.

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

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

Note, however, that the Visual Basic Err object only keeps track of error information for the main package thread and not for the step thread(s). Therefore, the Visual Basic Err object may not return all the error information reported by the DTS UI or DTSRun.

For additional information on handling and keeping track of DTS errors, please click the article numbers below to view the corresponding article from the Microsoft Knowledge Base:

221193 HOW TO: Install Data Transformation Services (DTS) Event Handlers in Visual Basic

Steps to Reproduce the Bug
  Create a table with a primary key in the SQL Server pubs database and import from a text file with a duplicate key: Create table DTS_Text ( Id int not null PRIMARY KEY, name char(5) not null ) go insert DTS_Text values ( 1, 'One') go insert DTS_Text values ( 2, 'Two') go insert DTS_Text values ( 1, 'Try') go  The last insert statement produces the error:

Server: Msg 2627, Level 14, State 1, Line 1

Violation of PRIMARY KEY constraint 'PK__DTS_Text__090A5324'. Cannot

insert duplicate key in object 'DTS_Text'.

The statement has been terminated.

 Create a text file named TextData.txt with the following rows of data:

 1, "One" 2, "Two" 1, "Fail" 3, "Try"

 Create a DTS package using the DTS Import Wizard to copy data from the text file created previously to the DTS_Text table within the pubs database. Use the SQLOLEDB provider to facilitate the data transfer within the DTS package and save it locally as Text_Import. Upon running the package, a detailed error message about a primary key violation is displays.  Create a new Visual Basic EXE project and add a reference to the Microsoft DTSPackage Object Library (DTSPkg.rll). Also, add a command button and paste the following code into the project: Option Explicit Dim WithEvents goPackage As DTS.Package Public bCancel As Boolean Public Sub ChangeAllStepsToRunOnMainThread(oPkg As DTS.Package) Dim nStepCount As Integer For nStepCount = 1 To oPkg.Steps.Count oPkg.Steps(nStepCount).ExecuteInMainThread = True Next nStepCount End Sub

Private Sub Command1_Click

On Error GoTo Error_Handler

Set goPackage = New DTS.Package

goPackage.LoadFromSQLServer ServerName:="(local)", ServerUserName:="sa", _ ServerPassword:="", PackageName:="Text_Import" ' Only call the following when developing the application. You ' can comment out the call when you build your application. ChangeAllStepsToRunOnMainThread goPackage

'goPackage.SaveToSQLServer "(local)", "sa", "" goPackage.Execute

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

Dim lpErrorCode As Long Dim i As Integer Dim s1 As String Dim s2 As String Dim s3 As String Dim gsDTSErr As String Dim j As Long For i = 1 To goPackage.Steps.Count If goPackage.Steps(i).ExecutionResult = DTSStepExecResult_Failure Then goPackage.Steps(i).GetExecutionErrorInfo lpErrorCode, s1, gsDTSErr, s2, j, s3           Debug.Print lpErrorCode, Debug.Print s1           Debug.Print gsDTSErr Debug.Print s2    End If  Next i  Set goPackage = Nothing End Sub

Private Sub Form_Load

End Sub

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 pbCancel = False End Sub

Private Sub goPackage_OnFinish(ByVal EventSource As String) Debug.Print "goPackage_OnFinish Fired" End Sub

Private Sub goPackage_OnProgress(ByVal EventSource As String, _                                  ByVal ProgressDescription As String, _                                   ByVal PercentComplete As Long, _                                   ByVal ProgressCountLow As Long, _                                   ByVal ProgressCountHigh As Long)

Debug.Print "goPackage_OnProgress Fired" End Sub

Private Sub goPackage_OnQueryCancel(ByVal EventSource As String, _                                      ByRef pbCancel As Boolean)

Debug.Print "goPackage_OnQueryCancel Fired" If bCancel Then pbCancel = True Debug.Print "Canceling package execution." Else pbCancel = False End If End Sub

Private Sub goPackage_OnStart(ByVal EventSource As String) Debug.Print "goPackage_OnStart Fired" End Sub </li> When this project is run, 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 the primary key violation that was returned by the user interface:

<pre class="fixed_text">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.

</li></ol>

Keywords: kbbug kbfix KB251229

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.