Tuesday, 18 October 2011

SSIS : Continue Looping after a child task Failure

It is a very common scenario that we want the loop to continue even in case of a child task failing. We can implement this in the following manner.

1.       Create your loop and configure it

2.       Add a Sequence Container to that Loop

3.       Set the Sequence Container’s MaximumErrorCount Property To 0 (Zero)

4.       Create an OnError Event Handler for the Sequence Container

(You can create a custom error logging mechanism here)

5.       Set the system variable Propagate to False when in the Event Handler Page

You can see the system variables by clicking the gray button on the Variables Window

6.       Add your Child Task in the Sequence Container

In My case I have added a script task that is throwing a dummy Exception using
Throw New ApplicationException("Test Exception")

7.       Execute the package and done. The Script task will fail  but the Sequence container and the Loop Container will execute successfully