We help IT Professionals succeed at work.

How to solve an out-of-memory error in SSIS?

I need to create a snapshot table based on aggregation of about 1 million rows every day.  I think I have developed my package correctly, but it looks like it has an out-of-memory error (0x4004800D).

Is there any way to alleviate the problem without adding more physical memory, i.e. optimizing the ETL package?  Thanks.
Comment
Watch Question

SQL Server DBA
Top Expert 2011
Commented:
Try changing the DefaultMaxBufferRows to low values the default is 10000. Check SSIS Performance tuning  too.

Author

Commented:
The thing is the aggregation is based on that 1 million rows.  They all need to be loaded to the ram before they can be aggregated, right?
Commented:
Do you have any index on the column and is it being used?

Author

Commented:
I don't think so.
Commented:
You would llike to create them.

Author

Commented:
OK, but how would creating index help to solve the ram issue in my case?
AnujSQL Server DBA
Top Expert 2011
Commented:
Creating index on the aggregate columns will speed up the read.

Author

Commented:
OK, that makes sense, but my current issue is I'm trying to do an aggregation based on ALL rows (about a million) in a table.  That's to say the server needs to hold that million rows in the RAM before they can be aggregated.  The problem now is the RAM doesn't have enough space to hold every row.

Author

Commented:
Thanks.