SSIS out of memory exception

Topics: Issues 3: Executing the Component (Run-Time)
Dec 19, 2011 at 12:33 PM

Hi Todd,

I've a SSIS package with only the size of 562 KB. It reads a file and loads the data into SQL server table using Kimball transformation for SCD changes. The file size is around 430MB. When I run the package, it thows me Exception of type 'System.OutOfMemoryException' after reading some records.

I've  32 bit system and 3.6 GB physical memory but this problem starts occuring as soon as the memory reaches 2.36 GB. Can someone please help me to resolve this problem soon?

Regards,

Ashish

Dec 19, 2011 at 1:23 PM

Hi Ashish,

For troubleshooting I'd remove the Dimension merge SCD component, If you do a straight read from the file and write to a temp table do you still get memory issues? Try then adding a 'sort' component between the source and destination as well and see if that causes memory issue.  I've used the SCD component to read much more than 430MB of data at a time with no memory issues, so I suspect you have an SSIS issue more than a component issue.  

Dec 20, 2011 at 5:51 AM
Edited Dec 20, 2011 at 8:22 AM

Hi, thanks for your reply. I've tried to load the file to direct table and its working fine. Then I tried to add a sort in between file and target table, its running since last 2 hours but it didn't fail. However while running the original package (with Kimball transformation), it fails and shows the below message:

[Kimball Method Slowly Changing Dimension] Error: Internal Error (Exception of type 'System.OutOfMemory was thrown.') in ProcessInput sending row to Cache.

Also just to inform, I'm using "Kimball SCD v1.5" and not the latest "Dimension Merge" transformation.

Do you have any suggestions?

Regards,

Ashish