Tuesday 13 May 2014

Continue Loop after error - Part I

Case
I want to continue my loop when in one of the iterations a task fails. It should continue with the next iteration/file. I tried changing the task properties FailParentOnFailure and MaximumErrorCount, but nothing seems to work.
Task fails, so loop fails

















Solution
There are a couple of solutions. You could set the MaximumErrorCount to a higher number on the parent container (not on the task that fails). With FailParentOnFailure property on the failing task you can override that setting and fail the parent on the first error.

The easiest/stable solution is to use an empty OnError event handler on the failing task with the system variable Propagate set to false.

1) Empty event handler
Go to the event handlers tab. Select (1) the failing task as Executable and (2) OnError as the event handler. Next (3) click on the link in the middle of the page to create the event handler: Click here to create an 'OnError' event handler for executable 'DFT - Stage files'.
Create empty event handler
















2) System variable propagate
Open the variable pane in the event handler. Click on (1) the Variable Grid Options button. Check (2) the radio button "Show system variables". Search for the Propagate variable and set (3) it to false.
System variable Propagate

























Show system variables for SSIS 2008








3) Annotation
An empty event handler could confuse other developers. Adding a simple annotation could solve that.
Add an annotation























4) The result
Now an error in the Data Flow Task won't fail the Foreach Loop Container. This solution works with all containers (Sequence, For Loop, Foreach Loop and package).
























Note: this solution wont work with parent child packages. Propagate can't be disabled from a child package to a parent package. This is by design according Microsoft. Here is a workaround for that.

No comments:

Post a Comment

Please use the SSIS MSDN forum for general SSIS questions that are not about this post. I'm a regular reader of that forum and will gladly answer those questions over there.

All comments are moderated manually to prevent spam.

Related Posts Plugin for WordPress, Blogger...