May 6, 2009 at 3:42 pm
I am trying to figure out how MaximumErrorCount and ForcedExecutionValue settings seem to work with some tasks but not others.
For example, if I create a package that has an Execute SQL Task and test it by using a problematic statement like 1/0 or referencing an object that does not exist the next step will not execute regardless of the MaximumErrorCount I set or if I set the ForceExecutionValue to return a 0. The package MaximumErrorCount has also been set to a higher value.
I am trying to figure out the whys and wherefors of this...
Any thoughts would help. Also, are there any other SSIS tasks that seem to share this behavior?
April 9, 2010 at 9:40 am
Hello Thomas,
After some investigation on the subject you mencioned I came to a conclusion about this.
I tried Error Handling with Execute SQL Tasks, Bulk Insert Tasks, Data Flow Tasks, Execute Package Tasks and Script Tasks.
The behaviour was identical in all except one: The Script Task! I mean by default even the script task has the same behaviour but you can change it as I will show bellow...
Q1: So what was the behaviour for all the tasks?
A: Setting the MaximumErrorCount to a higher value (than the actual number of errors) doesn´t keep the tasks from failing. So no matter the value in MaximumErrorCount the tasks that had errors would fail and the flow would stop there.
But if you want you can set the ForceExecutionResult property to "Success". Now this won't avoid the errors from occurring but it will allow the task to still succeed and the flow to proceed.
Q2: What about the script task?
A: Errors appear in the progress tab as well as in ssis logging (if enabled). This is true for all the tasks in a package and for the package itself.
The question is that in a script task you can use a try-catch block in order to catch and treat the errors. If you do so, then the script task will succeed but the error will not be sent to the progress tab (or ssis logging).
Now supose that you treat the error in a try-catch block (because you don't want the task to fail) but you would also like to send the error to the progress tab for logging consistency purposes.
How can you achieve this?
Simple! take a look at this sample code in the script task:
public void Main()
{
try
{
int i = 20, j = 0;
i = i / j;
}
catch
{
Dts.Events.FireError(0, "My Script Task", "Division by Zero", "", 0);
}
Dts.TaskResult = (int)ScriptResults.Success;
}
Now just set the MaximumErrorCount property of the script task to a value greater than 1 and voilá!
You're catching the error (avoiding the task to fail) and you're firing the OnError Event to make it appear on the progress tab. Since the value of MaximumErrorCount is greater than 1 all goes well!
April 9, 2010 at 12:54 pm
I think there is also something else to consider, a step failing isn't necessary a package fail, there are options to make it so. If you don't have a fail path in control flow then it is a fail but you can take a different action if it is fail. I rarely ever use ForceExecutionResult, I'd rather have the task fail and handle it, or if I don't care I use the completion precendence.
CEWII
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply