FailParentOnFailure and MaxErrorCount - not functioning as I would expect
I have a package that executes a number of tasks includng executing other packages. In one of the sub-packages, I am looping through a directory, processing files in that directory, and copying the files to another directory after completion using a for each file enumerator and file system task. The file system task has a failure precedence constraint leaving it going into a script task which simply pops up a message box saying that an error occurred. What I am trying to do is to allow the sub package (and parent package) to continue execution if there is an error during the file system task. This question really is unrelated to the file system task specifically... in general, how can an error be ignored and execution not stop? On the file system task, I've set MaxErrorCount = 0 and FailParentOnFailure and FailPackageOnFailure are set to False. With MaxErrorCount = 0, the file system task itself does not fail but its parent container (the for each file enumerator) does. This failure then propogates all the way up to the parent package and execution stops. I have gotten around this by setting MaxErrorCount = 0 also on the for each file enumerator, the execute package task, and all its ancestors going all the way up to the root of the main package. This works, but maintaining this would be a nightmare. Setting MaxErrorCount = 0 and FailParentOnFailure = False at the point only where I expect the error seems like it should be enough but it obviously is not. What am I missing here?
April 1st, 2008 12:18am

So I think I finally have a grasp on what's going on here. When the file system task fails, the OnError event is propagted to all of its ancestor executables (regardless of the file system task's settings for FailParentOnFailure and MaxErrorCount). For each executable, this causes the error count to increment. Since the MaxErrorCount is set to 1 for each of these components, the components then fail causing the package to stop execution. What I was attempting to do was log all errors in the packages' event handler, but then have the package continue execution. Seems like the only way to do this is to set the MaxErrorCount all the up the path to the root to 0. I definitely do not want to do this, so back to the drawing board. As an aside, what happens if FailParentOnFailure is True but the Propogate variable for the OnErrorHandler is set to False? I've tested this, and it looks like the parent does indeed fail as I would expect. Error handling is a lot more subtle than it looks on the surface. I've yet to find a really good article online about this (hint, hint to those of you with the popular blogs). Perhaps one already exists??
Free Windows Admin Tool Kit Click here and download it now
April 1st, 2008 9:42pm

There is an easy work around for this. Case 1: If indeed you want the package execution to stop if an error is generated from a certain executable 1, set the Precedence Constraint following the executable 1 to 'Success'. This will cause the package to stop executing and catch the error because of the 'Failure' Precedence Constraint you have also added to the executable. Example: Executable 1 is a Transact SQL Task that dumps a table's contents into a .csv file. Executable 2 is an FTP Task that uploads the .csv file to a designated FTP server. You therefore to not want to continue to Executable 2 because it will be old data. Case 2: If you need the entire package execution to complete regardless of any errors generated from a certain executable, set the Precedence Constraint of all the executable to 'Completion'. This will cause the package to continue executing and still catch the error because of the 'Failure' Precedence Constraint you have also added to the executable. Example: All executables is the package may fail without having a domino effect on the rest of the package. In this case add a 'Failure' Precedence Constraint to all executables that needs error catching and change all the flow Precedence Constraints from on executable to another to 'Completion'. A better way for catching errors below: A good article for error reporting and concatenating multiple errors generated in a SSIS package is: http://agilebi.com/cs/blogs/jwelch/archive/2008/01/15/handling-multiple-errors-in-ssis-revisited.aspx Be sure to read the comments as well. Lataz
April 15th, 2009 12:26pm

Thanks for the tip on setting the Propagate variable on your OnError event handler to False. All I wanted to do was to ignore the failure of an FTP task when it comes to determining the execution result of the whole package. My package has a loop that tries to grab a file off a remote FTP server repeatedly until it succeeds. The only problem was that even if I tried forcing the exec result to success and and set FailParentonFailure to false, the package would still fail. Adding an event handler for OnError events on the FTP task and setting the Propagate var to false did the trick. CalgaryCoder
Free Windows Admin Tool Kit Click here and download it now
March 23rd, 2010 12:41am

Additional "documentation" on the FailParentOnFailure/FailPackageOnFailure properties: https://connect.microsoft.com/SQLServer/feedback/details/415056/failparentonfailure-is-ignored-for-data-flow-tasks http://connect.microsoft.com/SQLServer/feedback/details/381681/ssis100-failparentonfailure-failpackageonfailure-are-erroneously-regarded-to-be-always-trueMCITP SQL Server 2008 (DEV); MCTS SQL Server 2008 (BI, DEV & DBA)
May 9th, 2012 2:21am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics