none
Large XML file source in SSIS????

    Question

  • Hi,

    I have a problem where I want to import a 1.6 GB XML file with SSIS into a SQL Server database. My hunch is that SSIS is not very good with handling such large amount of XML data. My test shows that SSIS tries to read all of the file into memory.

    Does anyone know if there is any solution of solving this memory problem. My problem is that I want to take this source XML file import it into a database, make some transformations on it (eliminate duplicates etc) then produce a NEW XML file as output in a different XSD-format.

    Is really SSIS the right tool for this operation?

    The source XML file also have mixed content on Complex Types which seems to be a problem for SSIS as well.

    Best regs,

    //Patrick

     

    Friday, January 19, 2007 12:43 PM

All replies

  • Which SSIS approach did you try, the XML Task, or the dataflow with an XML source?  Presumably it was the task, because of stock SSIS xml source component's inability to handle mixed content?

    The XML Task, in my experience, croaks on large XML files, and also doesn't work in loops if there is a single failure. 

    On the other hand, I have used the XML source in an SSIS dataflow with relatively large files, 100Mb or so, without a problem, but have never tested with Gb+ sized files.

    SQLXmlBulkLoad, on the other hand, works fine with large xml files, but there again, I'm not certain about mixed content.  I have posted a script task which uses SqlXmlBulkload in another forum post.

    Also, what version of SQL Server  are you using, since there a number of additional options in 2k5?

    Saturday, January 20, 2007 12:52 AM