August 11, 2005 at 8:55 am
I have a DTS Package set up which processes two files. File1 is always present. File2 may or may not be present. So, the Package is set up to processe File1 then perform a file exists check for File2 using an ActiveX Script similar to:
If fso.FileExists(filename) Then
Main = DTSTaskExecResult_Success
Else
Main = DTSTaskExecResult_Failure
End If
If File2 does not exist the ActiveX Task sets the Package Status to a Failure and then (in the next step) sends a Warning Email notifying the users that File2 does not exists. I want this to be just a Warning Status not a Failure.
The Job Status also reflects a Failure and it makes it seems that the Job failed when it really did not. Is there any way to control the workflow with something other than On Failure? (The On Success workflow is being used in this step and I do not think On Completion would work.) We are using SQL Server 2000 SP3.
Thanks for you assistance in advance, Kevin
August 11, 2005 at 5:08 pm
Kevin,
This article on SQLDTS.com describes your problem and suggests using the DTSStepScriptResult constants to get around it. I haven't used it myself, but remembered seeing it on the site.
Greg
http://www.sqldts.com/default.aspx?211
Greg
October 4, 2005 at 2:06 pm
Hi guys,
It looks like I have exactly the same problem as Kevin did.
Greg, thanks for the link.
I have tried both solutions from the article, but still not getting what I need.
"DTSStepScriptResult_DontExecuteTask" constant works fine to prevent a task from executing, but the problem is that the package execution also stops at this task.
To illustrate it, please look at DTSRun output for two different scenarios.
1) In first case, the file I'm checking for DOES exist, DTSStepScriptResult_ExecuteTask allows the "DTSStep_DTSBulkInsertTask_3" task to execute, and the rest of tasks after this task are also executed:
DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSDynamicPropertiesTask_1
DTSRun OnFinish: DTSStep_DTSDynamicPropertiesTask_1
DTSRun OnStart: DTSStep_DTSActiveScriptTask_1
DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1
DTSRun OnStart: DTSStep_DTSBulkInsertTask_1
DTSRun OnFinish: DTSStep_DTSBulkInsertTask_1
DTSRun OnStart: DTSStep_DTSBulkInsertTask_2
DTSRun OnFinish: DTSStep_DTSBulkInsertTask_2
DTSRun OnStart: DTSStep_DTSBulkInsertTask_3
DTSRun OnFinish: DTSStep_DTSBulkInsertTask_3
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1
DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1
DTSRun OnStart: DTSStep_DTSActiveScriptTask_2
DTSRun OnFinish: DTSStep_DTSActiveScriptTask_2
DTSRun: Package execution complete.
2) In this case, the file does NOT exist, DTSStepScriptResult_DontExecuteTask prevents the task from executing, but the package also stops. In other words, it does not complete tasks "DTSStep_DTSExecuteSQLTask_1" and "DTSStep_DTSActiveScriptTask_2", which must be run regardless of task "DTSStep_DTSBulkInsertTask_3."
DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSDynamicPropertiesTask_1
DTSRun OnFinish: DTSStep_DTSDynamicPropertiesTask_1
DTSRun OnStart: DTSStep_DTSActiveScriptTask_1
DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1
DTSRun OnStart: DTSStep_DTSBulkInsertTask_1
DTSRun OnFinish: DTSStep_DTSBulkInsertTask_1
DTSRun OnStart: DTSStep_DTSBulkInsertTask_2
DTSRun OnFinish: DTSStep_DTSBulkInsertTask_2
DTSRun OnStart: DTSStep_DTSBulkInsertTask_3
DTSRun OnProgress: DTSStep_DTSBulkInsertTask_3; Step was not run; status was set to Inactive.; Perc
entComplete = 0; ProgressCount = 0
DTSRun OnFinish: DTSStep_DTSBulkInsertTask_3
DTSRun: Package execution complete.
("Fail package on first error" box is, of cause, unchecked in the package properties)
Am I missing something?
Any help would be appreciated.
Thanks,
Andrey
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply