SSIS loading entire XML before starting write to SQL Server

Navigation
7

I need to load a single large (40GB +) XML file into a SQL Server 2012 table using SSIS. I'm having problems because SSIS seems to be trying to load the entire XML file into memory before loading records into the database, instead of reading and loading portions of the file and releasing the memory.

At the moment, what I have is a task with an XML Source, with the following properties:

  • Data access mode: XML file from variable (but could have been configured as XML File Location)
  • Variable name: name of the variable that specifies the name of the XML file
  • XSD location: the path to the XML file's validation XSD file.

The structure of my XML file is simple. It has only the 3 hierarchical levels, as follows:

  • Root element, with information from the file header (who sent the file, date, etc.)
  • The next level defines object collections.
  • The last level has the objects individually, with a fixed set of fields.

    • I need to insert one record per object at the last level of the XML, replicating the values of the elements above in the hierarchy. That is, I'm making the XML hierarchy a flat file.

    My question is: How do I load this file with SSIS, without trying to load the whole file into memory?

        
    asked by anonymous 13.12.2013 / 02:51

    1 answer

    2

    XML Source does not read the entire document but an element of the hierarchy described in the XSD. Ensure that the document contains a single root element, as documented in XML Source - at MSDN
    Test with a smaller sample portion of the document.

    Follow a step-by-step where an XML document containing all Wikipedia articles was imported into a SQL Server database using Integration Services. It is a bank of 20GB at the time.

        
    13.12.2013 / 13:41
    How to paste text from clipboard into vim? How to create a simple markdown with PHP?