Take the 2-minute tour ×

I am using a foreach loop in SSIS to iterate over the contents of a folder, executing a SQL Task for each file.

When I do this, it works fine the first ~300 times through the loop, then it hangs and says:

Error: 0xC002F210 at ______, Execute SQL Task: Executing the query "[__].[dbo].[_______]" 
failed with the following error: "Timeout expired.  The timeout period elapsed prior to 
were in use and max pool size was reached.". Possible failure reasons: Problems with the 
established correctly.

I'd imagine that this means that the old tasks are closing too slowly? The real kicker is that if I then add another SQL Task (inserting a blank string into a tmp table, using the same connection) before the task that is failing, the whole thing will work just fine. This isn't a good solution though, and I am hoping to figure out if there is a known workaround or if I am doing something very foolish.

share|improve this question
add comment

1 Answer

up vote 2 down vote accepted

There's an option in the connection manager to reuse the existing connection. Maybe changing this setting will help.

Select properties for your connection manager and set the RetainSameConnection property to True.

share|improve this answer
Dang! I thought that this was going to work for sure! Hadn't seen that property. Somehow, I'm now getting 15 more items processed, but it still crashes! –  nosirrahcd Dec 22 '10 at 16:51
I take that back--I was using an XML config file and had to change the retainSameConnection property in that too; now it works! Thanks! –  nosirrahcd Dec 22 '10 at 21:46
Thank you very much, you saved my day! –  Igor Oct 2 '12 at 8:39
add comment

Your Answer


