SSIS package execute - Out-of-memory issue!!

Question

While executing SSIS package, I got following errors,

The buffer manager failed a memory allocation call for 10484608 bytes, but was unable to swap out any buffers to relieve memory pressure. 20 buffers were considered and 20 were locked. Either not enough memory is available to the pipeline because not enough are installed, other processes were using it, or too many buffers are locked.

[DTS.Pipeline] Error: Thread "SourceThread0" has exited with error code 0xC0047038.

[DTS.Pipeline] Error: The Data Flow task failed to create a buffer to call PrimeOutput for output "XML Source 1" (91) on component "GeneralCongfigurations" (98). This error usually occurs due to an out-of-memory condition.

It happen when trying insert data to SQL table from an XML file with 'Script Component'.

How to solve it?

Answer 1

The message tells you SSIS is using 20 buffers, 10Mb each - about 200Mb together. It is not very big amount of memory, even on 1Gb machine you would not run out of memory.

It is likely other processes consume the rest of the memory - check the task manager. Often it is SQL Server who consumes all the memory - if you run SQL and SSIS on same machine, restrict the amount of memory SQL is allowed to consume (in SQL Server properties), leaving some memory for SSIS - I would recommend leaving at least 0.5Gb.

Answer 2

You should also look at all of the log messages. In particular, Lookup transforms can log a lot of information about how much memory they're using. You can get some very detailed logs about memory allocation.