Large XML file source in SSIS?

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

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?

Large XML File

I would like to create a history table that stores xml files. The xml files size are in the range of 300 kb to 600 kb. What is the best approach to do this? Any suggestion will be appreicated.
What do you want to do with these XML documents? Are you just looking for a
safe place to keep them or are you planning to use the database to search
the contents of the files? If they are history files, is it enough to be
able to retrieve them by date or do you need to be able to search through
the contents of the file? Do they follow a fixed schema or are they of
random structure?
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Abi" <anonymous@.discussions.microsoft.com> wrote in message
news:072255ED-F5A2-4C1E-B7AA-FACED3034D64@.microsoft.com...
>I would like to create a history table that stores xml files. The xml
>files size are in the range of 300 kb to 600 kb. What is the best approach
>to do this? Any suggestion will be appreicated.