Foreach Loop Container causes package to crash

We have a problem with a SSIS package containing a Foreach Loop Container that causes the package to fail unpredictably.

We are using a Foreach Loop Container to process records in a source table one by one. We do this by executing a SQL statement on the source table, putting the resultset in a package variable and using that variable as an ADO object source variable in a Foreach Loop Container. In that container, we do four things:

1) copy the record we want to process into a temporary table,
2) run a dataflow task on that temporary table to actually process the record,
3) truncate the temporary table filled in step 1 and
4) delete the processed record from the source table.

This part of the package validates and runs fine, but every now and then the package fails somewhere in the Foreach Loop Container without any useful notification. We cannot tell where exactly the package fails: it differs. It's often in the dataflow task, but not always. If we clean up the step in which the package fails and rerun it (such that the last row in the source table is processed again), it continues without a problem. Sometimes it stops in the middle of processing a specific record. If we leave the record in the source and process that record again, it processes fine without failing on that record again. So it's not one of the source records causing the problem. One time it will take a couple of hundred iterations before the failure occurs, the next time it might take less than a hundred.

Does anybody have any clue on what might cause this problem or what we can do to further investigate this?

Thanks in advance, Hans Geurtsen

Does "without any useful notification" mean that no errors are shown or that you don't find the error(s) useful. If there are errors then can you please provide them. Without the errors all I can do is hazard a guess. Perhaps you are encountering locking issues or perhaps there are memory problems due to fragmentation.

Thanks,

Matt