2 Mar 2006

SSIS File handle leak in For Each Loop

I am currently working on a project which needs to load over a 1000 xml files. The files are stored across 10 subfolders. I am using a foreach loop with a file enumerator, which is configured at the top of the folder structure and traverses the subfolders. This loops through the files, load the data and then moves the file to another folder. The package executes fine for a few 100 files but then hangs; this happens after a different number of processed files each time the package is run. While trying to resolve the problem we ran performance counters and noticed that the number open handles increased significantly just about the time Dtexec looked like it had hanged and DTexec also then started taking a lot of the cpu processing time.

Update: I put this on the Microsoft forums and got a intresting answer back See Here;
There seems to be a memory leak with foreach loop which should be fixed in sp1.


Rob said...

Did this memory leak ever get fixed in SQL Server? I still seem to be having a memory leak using a ForEach loop in SSIS with SQL Server 2005 SP2.

Anonymous said...

Similar problem here. I'm looping thru a Full result set (only 2 records) from an Execute SQL Task. Sometimes the loop completes - sometimes it hangs (90%) (while running in the BIDS UI). It seems to hang less (25%)if I re-reference the source variable for the ADO Enum (using the design UI), then rebuild, but it still hangs sometimes. The behavior seems very inconsistent - sometimes the loop completes, sometimes it doesn't. Very confounding. I'm still on SP1 - I'll try SP2...