ForEach Loop Container – Continue On Error


Download Sample Package and SQL Files[Extract the zip file – SSISContinueOnError.zip containing SQL Files to C:\EasyBI such that all SQL files reside directly within C:\EasyBI\SSISContinueOnError]

Lets say we use For Each Loop Container for looping around a set of files – say 3 files – and do an operation  – like running the SQL text in all the files against a DB Connection. In case if the operation fails in the 2nd file, the container will fail and stop processing the remaining files. So 3rd file would not process by default.

How can we ignore the Error in one of the files and Continue processing other files? To do this, Lets understand why the For Each Loop container fails in the first place.

For Each Loop Container fails because the failing tasks in the container propagates / bubbles the error to the parent container. To ensure the processing continues, we need to stop the propagation.

We need to set the System::Propagate property of the SQL Task’s On Error Handler to false. Refer this URL for detailed information. This setting ensures that the SQL task Does Not Propogate error to the parent container (in this case For Each Loop Container).

The same behaviour would be applicable for any other type of For Each Loop Enumerator such as ADO Enumerator.

Download the package and try yourself.

Advertisements

About EasyBIJegan

Business Intelligence Developer
This entry was posted in SSIS. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s