SSIS XML Stream Reader

• IRL11

  SSC Enthusiast

  Points: 158

  More actions

  August 25, 2016 at 5:51 am

  #312522

  Hi All,

  I am looking to try and figure out a solution to load and process large and complex XML files into SQL. The standard SSIS XML Source is unable to handle the complexity of the documents (3x XSD's and multiple namespaces and 100+ node possibilities). My preference would be to avoid writing the XML files into an XML blob as many of the files are 1GB+ and this adds additional complexities as well as having storage impacts.

  A possible solution which I came across was in Pentaho, where we could use the XML Input Stream (StAX) to produce a simplified node/attribute branching into SQL in a generic format (see first pic), this was generated based on the XML from the second pic. (samples are of a simple xml file, however this solution is scalable to very large and files with complex nesting etc).

  Is it possible to produce something similar with SSIS? I haven't had to do too much with XML other than relatively straight forward files, so when this one came up it threw me quite a bit.

  Does anyone know of a way to do this with SSIS or can you advise on how to create a customer Script Source to work this out?

  We are using SQL 2014 and are looking to upgrade to 2016 shortly, so a possible solution that will work with either would be ideal.

  Thanks for your help,

  Steve