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.
