HELP! DTS Error | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive


I have a DTS job in SQL 2000 that has been completing with errors recently.<br />Here is part of the log showing the error.<br />——————————————————————————–<br />Step ‘DTSStep_DTSTransferObjectsTask_1’ succeeded<br />Step Execution Started: 06/11/2006 12:00:21 AM<br />Step Execution Completed: 06/11/2006 12:10:55 AM<br />Total Step Execution Time: 633.828 seconds<br />Progress count in Step: 0<br /><br />Step ‘DTSStep_DTSActiveScriptTask_2’ was not executed<br /><br />Step ‘DTSStep_DTSActiveScriptTask_3’ succeeded<br />Step Execution Started: 06/11/2006 12:00:21 AM<br />Step Execution Completed: 06/11/2006 12:00:21 AM<br />Total Step Execution Time: 0.031 seconds<br />Progress count in Step: 0<br /><br />Step ‘DTSStep_DTSDataDrivenQueryTask_1′ failed<br /><br />Step Error Source: Microsoft OLE DB Provider for SQL Server<br />Step Error Description<img src=’/community/emoticons/emotion-2.gif’ alt=’:D‘ />uplicate key was ignored.<br />Step Error code: 80040E2F<br />Step Error Help File:<br />Step Error Help Context ID:0<br /><br />Step Execution Started: 06/11/2006 2:45:31 AM<br />Step Execution Completed: 06/11/2006 2:45:37 AM<br />Total Step Execution Time: 6.375 seconds<br />Progress count in Step: 3000<br /><br />————————————————————————–<br /><br />Any ideas???<br />Thanks,<br />Mark<br />
Check whether KBA & is any help. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
This posting is provided AS IS with no rights for the sake of knowledge sharing.
Thanks for the reply Satya.
The articles you refer to don’t seem to resolve this particlular issue.
For one thing I should mention this is SQL 2000 sp3a.
I’ve done some more research and I’ve found the ‘DTSStep_DTSDataDrivenQueryTask_1’ script that is generating the error.
Here is the script:
Function Main() Dim oPackage
Dim intStepStatus
Set oPackage = DTSGlobalVariables.Parent ‘ Log Parameters
oStepName = oPackage.Steps("DTSStep_DTSDataDrivenQueryTask_1").Name
oStepStart =oPackage.Steps("DTSStep_DTSDataDrivenQueryTask_1").StartTime
oStepFinish = oPackage.Steps("DTSStep_DTSDataDrivenQueryTask_1").FinishTime
oStepExec =oPackage.Steps("DTSStep_DTSDataDrivenQueryTask_1").ExecutionTime ‘ Step Execution Result
oStepExecutionResult = oPackage.Steps("DTSStep_DTSDataDrivenQueryTask_1").ExecutionResult ‘ Put in container
intStepStatus = oStepExecutionResult ‘ Specify a limit for the number of retries otherwise there could be a infinite loop
If DTSGlobalVariables("EventConsolidatedRetryCounter").Value <= DTSGlobalVariables("MaxRetry").Value Then If cint( intStepStatus ) = 1 Then ‘ Log the error to a text file
Call LogEvent ( oStepName, oStepStart, oStepFinish, oStepExec, " failed " ) DTSGlobalVariables("EventConsolidatedRetryCounter") = DTSGlobalVariables("EventConsolidatedRetryCounter") + 1 ‘ Restart the previous task (workflow task)
oPackage.Steps("DTSStep_DTSDataDrivenQueryTask_1").ExecutionStatus = DTSStepExecStat_Waiting ‘ Dont execute the current task
Main = DTSStepScriptResult_DontExecuteTask
‘ If the step has succeeded, log success.
‘Call LogEvent ( oStepName, oStepStart, oStepFinish, oStepExec, " succeeded " ) Main = DTSStepScriptResult_ExecuteTask
End if
‘ Reset the task retry counter as the entire package may be re-run
DTSGlobalVariables("EventConsolidatedRetryCounter") = 1
Main = DTSStepScriptResult_ExecuteTask
set oPackage = nothing
Main = DTSTaskExecResult_Success
End if End Function Function LogEvent( strStep, strStartTime, strFinishTime, strExecTime, strStatus ) Dim fso
Dim fsoFile Set fso = CreateObject( "Scripting.FileSystemObject" ) strLog = DTSGlobalVariables("TargetDirectory").Value + DTSGlobalVariables("LogFileName").Value Set fsoFile = fs:confused:penTextFile(strLog, 8, 0) fsoFile.WriteLine ( strStep & strStatus & "on " & CStr(Now) & ". Start Time: " & strStartTime & " Finish Time: " & strFinishTime & " Execution Time: " & strExecTime & " Retry number: " & CStr(DTSGlobalVariables("Counter19").Value) ) fsoFile.Close set fsoFile = nothing
set fso = nothing End Function
Judging by the ‘duplicate key’ error and by looking at the script, I thought the issue was a duplicate row in the ‘eventconsolidatd’ table, but I queried for duplicate rows in that table and couldn’t find any.
Any ideas?????
Thank You!