SSIS package fails with out of memory errors

You might get below out of memory errors when you run SSIS packages 

Errors

A buffer failed while allocating n bytes. The system reports n percent memory load. There are n bytes of physical memory with n bytes free.

There are 2147352576 bytes of virtual memory with 44814336 bytes free. The paging file has 687069143034 bytes with 35348451328 bytes free.

A buffer failed while allocating 63160 bytes. The attempt to add a row to the Data Flow task buffer failed with error code 0x8007000E. 

SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on component “Read Results” (975) returned error code 0xC02020C4.  The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.  There may be error messages posted before this with more information about the failure. 

The system reports 2 percent memory load. There are 206147428352 bytes of physical memory with 24692375552 bytes free. There are 8796092891136 bytes of virtual memory with 8713333325824 bytes free. The paging file has 205966053376 bytes with 15835136 bytes free.

Executed as user: Domain\Password . …9.00.4035.00 for 32-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  6:06:21 AM  Error:     Code: 0xC0047012     Source: Load Daily Run Piece DTS.Pipeline     Description: A buffer failed while allocating 6356480 bytes.  End Error  Error: 2009-12-29 06:19:41.56     Code: 0xC0047011     Source: Load Daily Run Piece DTS.Pipeline     Description: The system reports 82 percent memory load. There are 4294656000 bytes of physical memory with 756637696 bytes free. There are 2147352576 bytes of virtual memory with 399495168 bytes free. The paging file has 8418062336 bytes with 5051555840 bytes free.

The system reports 24 percent memory load. There are 68716240896 bytes of physical memory with 51754737664 bytes free. There are 2147352576 bytes of virtual memory with 146911232 bytes free. The paging file has 137430540288 bytes with 120562003968 bytes free.

Below are options you can use to solve the above errors

1. Check if the Page file for the system is configured properly, you might get this error if there is no page file or if page file is very small

2. Change the lookup cache mode from Full to Partial or no cache mode.

3. Migrate to 64-Bit SSIS if you are in 32-BIT. Remember you have to migrate all the drivers and providers used in connections. If you are in 64 BIT system and getting above errors while executing packages from BIDS then check if you have set package to run in 64bit run time ( Run64bitruntime = True in  Integration services project properties page).

4.  Divide the package in to multiple child packages (Execute package task –> ExecuteOutOfProcess =True)

5. Tweak DefaultBufferMaxRows and DefaultBufferMaxSize size.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s