SSIS Data Flow: Buffer manager detected that system was on low virtual memory
<p>I have SSIS Data flow task to generate an xml for sql table and store it using flat file connection. The xml can contains more than 1 million records and the size can be around 1.5 gb (not more than 2gb that sql server permits for xml variable)</p>

<p>I am using FOR XML PATH to generate the xml</p>

<p><br>
DefaultBufferSize: 104857600</p>

<p>EngineThreads:50</p>

<p><br>
<strong>The task keeps on failing with error:</strong></p>

<p>The buffer manager detected that the system was low on virtual memory, but was unable to swap out any buffers. 0 buffers were considered and 0 were locked. Either not enough memory is available to the pipeline because not enough is installed, other processes are using it, or too many buffers are locked.</p>

<p>Buffer manager allocated 100 megabyte(s) in 1 physical buffer(s).</p>

<p>Component "Extract Sql Staging Data in XML Format" (2) owns 100 megabyte(s) physical buffer.</p>

<p>SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "Memory allocation failure".</p>

<p><img src="https://i.stack.imgur.com/xKbo7.png" alt="Data Flow Task"></p>