SSIS loading entire XML before you start writing to SQL Server

Asked

Viewed 860 times

7

I need to load a single large XML file (40GB+) into an 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, rather than reading and loading parts 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 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 validation XSD.

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

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

I need to insert a record per object at the last level of XML, replicating the values of the above elements in the hierarchy. I mean, I’m turning the XML hierarchy into a flat file.

My question is: How do I load this file with SSIS, without it trying to load the entire file in memory?

sql-server xml ssis

  • How is the SSIS package structure? You are using XML data conversion/transformation tools?

  • @Ricidleivtondatto the structure is as simple as possible. I have a Data Flow Task which in turn has a Source XML linked to an OLE DB Destination. I was going to include a screen copy but I couldn’t. It’s just in the same description...

  • You are using OLEDB or ADO.NET at Source?

  • @Ricidleivtondatto - From what I understand, the XML Source has nothing to do with database but with XML files, so source does not use OLEDB nor ADO.NET. Or the ADO.NET handles XML files too? Already at destination I am using OLEDB.

1 answer

2

The XML Source does not read the whole document, but rather an element of the hierarchy described in XSD.
Ensure that the document contains a single root element, as documented in XML Source - at MSDN
Take a test with a smaller sample part of the document.

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

  • In accordance with that reply (http://stackoverflow.com/a/20552229/2839597), XML Source reads the entire document. We do have several other smaller files that follow the same XSD (about 100) and these are loaded smoothly by our SSIS package. Thank you for the Wikipedia EN import link. I will study it now.

Browser other questions tagged sql-server xml ssis

You are not signed in. Login or sign up in order to post.