June 3, 2002 at 8:38 pm
I am getting the following errors from a ActiveX Script within a DTS package on a MS SQL 7 server:
DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSDataPumpTask_5 DTSRun OnProgress: DTSStep_DTSDataPumpTask_5; 1 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 1 DTSRun OnFinish: DTSStep_DTSDataPumpTask_5 DTSRun OnStart: DTSStep_DTSActiveScriptTask_1 DTSRun OnError: DTSStep_DTSActiveScriptTask_1, Error = -2147220479 (80040401) Error string: Invalid Task Result value. Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts.hlp Help context: 1100 Error Detail Records: Error: -2147220479 (80040401); Provider Error: 0 (0) Error string: Invalid Task Result value. Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts.hlp Help context: 1100 DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1 DTSRun: Package execution complete. Process Exit Code 1. The step failed.
The error only comes when the ElseIf comes into play. As long as the script stays in the first part there is no trouble. Here is the script:
Function Main()
' Declare Variables
Dim oPKG, fso, f
Dim oConnection
' Get Package Object
Set oPKG = DTSGlobalVariables.Parent
' Get Named Connection
Set oConnection = oPKG.Connections("CeltrolTrace")
set fso = CreateObject("Scripting.FileSystemObject")
If (fso.FileExists(DTSGlobalVariables("TraceFile").Value&DTSGlobalVariables("strDate").Value&"\trace."&DTSGlobalVariables("strHour").Value&DTSGlobalVariables("strMinute").Value)) Then
set f = fso.GetFile(DTSGlobalVariables("TraceFile").Value&DTSGlobalVariables("strDate").Value&"\trace."&DTSGlobalVariables("strHour").Value&DTSGlobalVariables("strMinute").Value)
'if f.DateCreated > dateadd("n",-18,now) then
' Set Filename to new value
'DTSGlobalVariables("strHour").Value =right("0"+cstr(hour(now)),2)
'DTSGlobalVariables("strMinute").Value=right("0"+cstr(int(minute(now)/6)*6),2)
oConnection.DataSource = DTSGlobalVariables("TraceFile").Value&DTSGlobalVariables("strDate").Value&"\trace."&DTSGlobalVariables("strHour").Value&DTSGlobalVariables("strMinute").Value
Set oConnection = Nothing
Set oPKG = Nothing
Main = DTSTaskExecResult_Success
ElseIf DateDiff("n",cdate(DTSGlobalVariables("strHour").Value&":"&DTSGlobalVariables("strMinute").Value),cdate(cstr(hour(now))&":"&cstr(int(minute(now)/6)*6))) > 6 then
DTSGlobalVariables("strHour").Value =right("0"+cstr(hour(dateadd("n",-12,now))),2)
DTSGlobalVariables("strMinute").Value=right("0"+cstr(int(minute(dateadd("n",-12,now))/6)*6),2)
If (fso.FileExists(DTSGlobalVariables("TraceFile").Value&cstr(DatePart( "yyyy", dateadd("n",-12,now)))&Right("0"&cstr(DatePart( "m", dateadd("n",-12,now))) , 2)&Right("0"&cstr(DatePart( "d", dateadd("n",-12,now))),2)&"\trace."&DTSGlobalVariables("strHour").Value&DTSGlobalVariables("strMinute").Value)) Then
set f = fso.GetFile(DTSGlobalVariables("TraceFile").Value&cstr(DatePart( "yyyy", dateadd("n",-12,now)))&Right("0"&cstr(DatePart( "m", dateadd("n",-12,now))) , 2)&Right("0"&cstr(DatePart( "d", dateadd("n",-12,now))),2)&"\trace."&DTSGlobalVariables("strHour").Value&DTSGlobalVariables("strMinute").Value)
' if f.DateCreated > dateadd("n",-30,now) then
oConnection.DataSource = DTSGlobalVariables("TraceFile").Value&cstr(DatePart( "yyyy", dateadd("n",-12,now)))&Right("0"&cstr(DatePart( "m", dateadd("n",-12,now))) , 2)&Right("0"&cstr(DatePart( "d", dateadd("n",-12,now))),2)&"\trace."&DTSGlobalVariables("strHour").Value&DTSGlobalVariables("strMinute").Value
'End If
Main = DTSTaskExecResult_Success
Else
Main = DTSTaskExecResult_Failure
End If
End If
Set oConnection = Nothing
Set oPKG = Nothing
End Function
October 24, 2006 at 6:24 am
i am also need explanation of samw error "Invalid Task Value"
October 24, 2006 at 8:22 am
It would help if the code was more readable
It appears that the condition that is causing the problem is when the file is not found and the datediff is not greater than 6. In this case, you are not returning a DTS package result value.
Try adding this line just before your last "End if" statement:
Else Main = DTSTaskExecResult_Failure
Your package should fail if it meets that specific condition.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply