0

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)

I am using FOR XML PATH to generate the xml


DefaultBufferSize: 104857600

EngineThreads:50


The task keeps on failing with error:

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.

Buffer manager allocated 100 megabyte(s) in 1 physical buffer(s).

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

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".

Data Flow Task

billinkc
  • 59,250
  • 9
  • 102
  • 159
vrooms_den
  • 1
  • 1
  • 1
  • The above picture indicates you are encountering this error from the context of Visual Studio/BIDS/SSDT. Use Ctrl-F5 (Debug menu, Start without Debugging) does it fail out similarly? Running a package from VS as you have done above adds a dedbugging layer to the processing. You're likely in a 32 bit address space so your process may be bumping up against the memory limit. Running w/o debugging should provide more room to grow. – billinkc Mar 10 '16 at 15:31
  • That said, you might be "doing it wrong". How many rows are you generating in from your source? Is that coming out as one large XML blob or many small XML pieces? – billinkc Mar 10 '16 at 15:32
  • Its all coming in one piece. There is 1 XML that needs to be generated for all the records – vrooms_den Mar 10 '16 at 18:38
  • You will want to use the Export Column Task as described in the duplicate and/or http://stackoverflow.com/questions/9104123/using-ssis-to-extract-a-xml-representation-of-table-data-to-a-file/9105756#9105756 this other candidate for duplicate. Otherwise, you are saturating your memory trying to carry a large object type along the buffers – billinkc Mar 10 '16 at 18:43
  • I had this problem today for the first time. I'm working on a multi-part package with slight complexity, and SSIS started throwing v-m errors. After initial troubleshooting steps, I had a a dumb idea: I exited Visual Studio and went back in. Problem solved. – spinjector Sep 11 '21 at 23:06

0 Answers0