Take the 2-minute tour ×

Is there a limit on the size of the result set of an Execute SQL task. If yes, then how does one deal with the excess portion of result set ?

Why am I asking this ? I intend to get 4-5 GB sized record set into memory via Execute SQL task (PREFERRED), use it to generate sql statements. These sql statements will then be used to load data into my destination table.

share|improve this question
Available memory and disk space are your only limits. Oh, and Time. That's likely to be the one that you run out of first for this approach. –  RBarryYoung Nov 1 '13 at 23:26
@RBarryYoung - How do I find the exact memory and disk space limits? How do I change them if need be ? –  Steam Nov 2 '13 at 15:41
There are not any exact limits. There only availability limits. You are limited to what's available, just as any process is. –  RBarryYoung Nov 2 '13 at 22:48

1 Answer 1

up vote 1 down vote accepted

As @RBarry Young states, your Execute SQL Task dumping results back is only going to be limited by the amount of memory on the machine/available to SSIS.

What happens when you run out of memory in a data flow, is that SSIS is going to use the location specified in BufferTempStoragePath to write data to disk and your performance tanks. What is the actual use of buffer temp and blob temp in ssis

I don't see an analog in the Control Flow, which makes sense since generally it's not the memory intensive section of code. Test it and find out. If you run out of memory and SSIS goes belly up, then you'll probably need to move your logic into an Script Task and then try to manage your memory for effectively than holding the whole set at once.

That said, I would almost think you're going about it the hard way building out your own insert statements. That'd be akin to writing your own data flow but probably not as efficiently as what the SSIS has done.

share|improve this answer
Thanks for all your points. I am almost 100% certain that I have no other alternative. My ETL is not straightforward. The source to target mapping is not fixed and can only be decided by querying a mapping table. There are other complexities too. Thats why I have to use mostly scripts. –  Steam Nov 3 '13 at 6:31

Your Answer


