FIX: GetExecutionErrorInfo and OnError Event May Not Return All Errors

This article was previously published under Q251229
This article has been archived. It is offered "as is" and will no longer be updated.
BUG #: 56362 (SQLBUG_70)
BUG #: 56363 (SQLBUG_70)
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.
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.
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

  1. 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 )goinsert DTS_Text values ( 1, 'One')goinsert DTS_Text values ( 2, 'Two')goinsert DTS_Text values ( 1, 'Try')go					
  2. 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.
  3. Create a text file named TextData.txt with the following rows of data:
     1, "One" 2, "Two" 1, "Fail" 3, "Try"						
  4. 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.
  5. 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 ExplicitDim WithEvents goPackage As DTS.PackagePublic 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 nStepCountEnd SubPrivate 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 = NothingEnd SubPrivate Sub Form_Load()End SubPrivate 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 = FalseEnd SubPrivate Sub goPackage_OnFinish(ByVal EventSource As String)     Debug.Print "goPackage_OnFinish Fired"End SubPrivate 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 SubPrivate 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 IfEnd Sub   Private Sub goPackage_OnStart(ByVal EventSource As String)       Debug.Print "goPackage_OnStart Fired"   End Sub					
  6. 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:
    goPackage_OnStart FiredgoPackage_OnError FiredDTS Data Pump-2147467259Error at Destination for Row number 4. Errors encountered so far in thistask: 1.goPackage_OnError FiredMicrosoft OLE DB Provider for SQL Server-2147217887The statement has been terminated.goPackage_OnFinish FiredgoPackage_OnQueryCancel Fired-2147217887  Microsoft OLE DB Provider for SQL ServerThe statement has been terminated.						

Article ID: 251229 - Last Review: 10/20/2013 16:58:37 - Revision: 2.1

Microsoft SQL Server 7.0 Standard Edition

  • kbnosurvey kbarchive kbbug kbfix KB251229