troubleshooting Question

Importing large / complex XML

Avatar of egarrison
egarrisonFlag for United States of America asked on
Microsoft SQL Server 2008SSIS
4 Comments1 Solution1012 ViewsLast Modified:
I need a little direction on a project.  An import is going from PIPE delimited to XML.  The Daily files are averaging 4GB.  I need to know the fastest way to import this large XML file. Not fast from setup; I have time to do this right, I mean fastest for the actually import.

It seems like XML Bulk Load should be one of the fastest:
http://support.microsoft.com/kb/316005

My issue is that the file is much more complex.  Some records may have 1 detail node, some 2000.  The data is Invoices (header info) and the Invoice details (1 to many rows).  

The second option is OpenXML.  http://msdn.microsoft.com/en-us/library/ms186918.aspx
This option seems like a close fit, but I don't have any experience or benchmarks for it.  The problem is that XML will be more flexible and better in the long run, but I need the speed I get from Bulk Load mode and BCP.  

Third option ? Suggestions. Maybe Third party software.  
I have tried SpectralCore's Full Convert Enterprise.  It could not handle the file.

Hardware: I cannot get much better hardware.  HP DL380G8.  128GB Ram, Netapp Fiber Storage.  SQL Server 2012 Enterprise.
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros