SSIS ForEach continuation after Error

ForEach in SQLServer Integration Services

Integration Services has built in constructs that provide the ability to loop through a result set.  This functionality can be used to loop through files in a directory, items returned from a query, or a list generated with another method.

One issue with the ForEach container, when using the default configuration, is that if one loop iteration fails the loop fails and processing stops.  Below is a technique that can be used to log the error and proceed to the next value in the list.

The package shown uses a ‘ForEach File Enumerator’ to loop through a directory, in this example c:\temp.  Three files exist in the directory that match the criteria.  LoopDemo.txt, LoopReadOnly.txt, and LoopSequential.txt.  LoopReadOnly.txt has the read only attribute set to true.

With the default options set for the ForEach loop construct the first file is deleted, LoopDemo.txt. When deletion on the second file is attempted, LoopReadOnly.txt, an error is produced and the loop fails after displaying the error.

By setting the ‘MaximumErrorCount’ to 0 on the ForEach container the failure will occur and the message displayed, however the loop will continue through the remaining file.

Errors produced by the task can be logged in any manner that is available, email notification, logging to a table etc.