SSIS 2012 Directory Memory Leak

Question

I have a package that has 1 for each loop container that has a bunch of inserts and selects.

The list in which the loop runs is about several million complex lines or so.

The package is located in the directory of integration services , where it is launched by simple execution in SSMS (without an agent job).

When I look at the resource monitor, the memory for ISServerExec.exe (comparable for dtsexec.exe) grows every second (it takes about 1 second to complete each cycle).

After some time, all the memory on the Windows server is used, and the server ends with a disk search. And then the wait time for the loop requests becomes huge, 20 - 30 seconds per request.

What am I doing wrong?

Answer 1

I would write a list to an SQL table and then a loop using a For Loop container wrapped around your container for each.

At the beginning of the For Loop container, I would read one entry from the list table using SELECT TOP 1, and pass it to the Recordset variable. The scope of this variable should be moved to the For Loop container.

At the end of the For Loop container, I would update the flag and / or datetime column to indicate that the row has been processed and should not be included in the next iteration of the original SELECT.

Along the way, you can update the list table to indicate the progress / status of each row.

This design is also useful for logging and restarting.