Microsoft KB Archive/251229

From BetaArchive Wiki
Knowledge Base

Article ID: 251229

Article Last Modified on 3/14/2006


  • Microsoft SQL Server 7.0 Standard Edition

This article was previously published under Q251229

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 )
    insert DTS_Text values ( 1, 'One')
    insert DTS_Text values ( 2, 'Two')
    insert DTS_Text values ( 1, 'Try')
  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", ""
    '  Package 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."
               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
    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
    The statement has been terminated.
    goPackage_OnFinish Fired
    goPackage_OnQueryCancel Fired
    -2147217887  Microsoft OLE DB Provider for SQL Server
    The statement has been terminated.

Keywords: kbbug kbfix KB251229