FIX: GetExecutionErrorInfo and OnError Event May Not Return All Errors

• Microsoft SQL Server version 7.0

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 version 7.0. This problem has been corrected in U.S. Service Pack 2 for Microsoft SQL Server version 7.0. For information about downloading and installing the latest SQL Server Service Pack, see http://support.microsoft.com/support/sql/.

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:

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

Q221193 INF: How To Install 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 )
go
insert DTS_Text values ( 1, 'One')
go
insert DTS_Text values ( 2, 'Two')
go
insert 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 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 

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 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. 

Additional query words:

Keywords : kbbug7.00 kbSQLServ700bug
Version : winnt:7.0
Platform : winnt
Issue type : kbbug