Using XML Source in SSIS vs Script Source to parse xml files.

Hari Ganeshan

Hi

I am having a huge xml file with nested section.

i also have a xsd file for that xml.

i have a destination table where the data from the xml should be loaded into.

i am using the xml source transformation. But o get all the data i need to use multiple merje joins to get the data in a single row which i can insert into the destination.i was not quiet convinced with using so many joins.

so i tried using the script source transformation where i am using xml objects to get the node and dynamically construction the data row. and the output is then inserted into the destination.

on comparing the two approach the one using the script source is working much faster than the xml source transformation.

i wanted to know is there any limitaion using the script source to parse through xml files.

also i would like to know any other better way of getting the data from xml source without using the joins.

Hari

Hari Ganeshan

Hi all

Can someone answer my question. please

jwelch

There are no limits in the Script Source with regards to parsing XML files that I am aware of. If you can do the XML processing in a .NET app, you should be to do it in the script.

Another approach would be to use the XML Task in the control flow to perform an XSLT operation to transform the XML into something that better suits your needs and works better with the XML Source.

John Saunders

I've been using the XML Source to process a large nested XML file. In my case, the contents need to be placed into several tables, so it's a little different from your situation.

On the other hand, my input XML is actually much more complex than it really needs to be. It's organized for ease of creation, not ease of consumption. If I had used that external-facing schema directly, the XML Source would have produced 149 outputs and 149 error outputs (yes, one hundred fourty-nine).

My solution was to run the XML through a transform to flatten it. This left me with only (!) 38 outputs and 38 error outputs.

Perhaps you can flatten your XML with an XSL transform before your XML source consumes it. You could use an XML Task to do this. Just have the task run before your data flow.

Also, be aware that there are several issues with the XML Source. Most of these appear to be proportional to the number of outputs, so you'll do well to limit the number of outputs.

Also, note that the XML transform completely invalidates its external metadata whenever it notices that the schema has changed. Even a trivial change will cause it to invalidate everything, requiring you to fix the metadata for all subsequent components. This usually just takes time and care, but once it forgot the names of the columns, which cost a day to fix.

Good luck. There don't appear to be many people using the XML Source.

Hari Ganeshan

Thanks for you input.

i am a bit weak in doing xml xsl. i didn't know that i can use an xsl to flatten my xml files.

i will try that approach. thanks again..