Loading Xml document into SQL tables from XmlSource - Wrote 0 Rows

• chris.nurse

  SSC Enthusiast

  Points: 172

  More actions

  February 18, 2011 at 7:51 am

  #157449

  Hi,

  I have an SSIS project which contains two packages. Each package processes a different XML document, and apart from the different XML document, the package structure and approach is identical. I validate the source document against the schema and its good. I use an XML source to load two sets of Xml Nodes (which are the outputs) (let's call them Customers and Orders for discussion as there name is top secret :-D) into two target SQL tables.

  My first package runs and writes heaps of data. My second package, which loads "Customers" and "Orders" runs without error but for both destinations it says "wrote 0 rows".

  I inserted a data viewed between the XML source and "Customers" and nothing comes up. So my assumption is that, although the schema says "Customers" is in the output, the XML document isn't getting parsed properly so no Customer nodes are actually located and sent down the pipe, hence data viewer doesn't fire.

  The whole thing is a very simple XML structure:

  <data>

  <Customers>

  <Customer>...nodes...</Customer>

  <Customer>...nodes...</Customer>

  </Customers>

  <Orders>

  <Order>...nodes...</Order>

  <Order>...nodes...</Order>

  </Orders>

  </data>

  All node names in the XML document map directly to identical column names in my SQL destination tables, so map using names works everytime.

  Does anyone have any good ideas on how to debug this or have suggestions as to where I might be going wrong?

  Cheers

  Chris.

• chris.nurse

  SSC Enthusiast

  Points: 172

  More actions

  February 21, 2011 at 9:39 pm

  #1289682

  I fixed this. Every time I think I am one step closer to guru status, I discover I've gone back two steps

  In my XML file, the namespace I had applied didn't match the namespace in the schema, hence when SISS searches for XML nodes to feed as outputs it finds zero nodes, so reports successful completion but writes no data.

  The root cause of all this was that I use an XSLT to transform an input message into a cleaner format which is easier to load through SSIS, so in my XSLT I had assigned a namespace a while ago, that since, has been refactored.

  Bingo! Hope this helps others in future, basically, ensure your XML validates against the schema and that the schema and namespace are assigned correctly in the XML.