I have a for each container configured to iterate over files in a directory
(around 2600 files). The for each container contains only one data flow task.
The data flow task is very simple. Only 3 steps. Read from file using flat
file source (ragged right), format column values (eg. converting YYYYMMDD
into yyyy/mm/dd and TRIM spaces - 13 such conversions) using derived column
transform and inserting them into a table (23 columns - mostly nvarchar, few
numerics and few dates) using Ole Db Destination. The table has one
non-unique clustered index on nvarchar(18) column. Each of the 2600 file has
at an average 750000 rows.

It seems to me that memory deallocation doesn't happen after each iteration
of the loop (I might be wrong). So after few iterations, sqlservr.exe is
hogging all available memory (2GB in this case) whereas my dual processors
are running at 3-6 % of capacity. As a result, to process less than 5 million
rows, it is taking 6 hours.

I am surprised that for such a trivial operation, performance should degrade
this much.

Any insight?

TIA,
Nitesh