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:
- 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
- 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.
Keywords: kbbug kbfix KB251229