SSIS XMl processing

For my job I do very big imports of (product) data. Recently we started using SSIS and it sure works better then custom .net import tools. Still after 3 projects we figured out it's more efficient to use an scripttask with c# xpath and sql statements then to 开发者_如何学Gouse XML source and merge joins in a dataflow.

Problems with a dataflow

• Sometimes the xml cannot read from a variable
• It depends too much on a fixed definition of XML (the xsd needs to be known)
• When definitions change a whole needs to be altered
• To specific type handling; UINT and INT arn't the same
• Not easy to debug and step through as C# code

Can anyone share their SSIS XML import stories

I work "all the way" with XML in SQL Server and mainly it is import feature in some of my projects.I do not use SSIS XML source because of limitation of XML structure. As example, it is not possible to import data from "Excel XML", where not only data but column names are as values. To prepare this kind of import, i use TSQL code. Maybe for this example you can use third-party components, but not all customers want to pay for that.))

You could try experimenting with some of the 3rd party components on CodeProject, or if your C#/VB skills are up for it, then consider rolling up your sleeves and writing a source script component to shred the xml using a SAX parser. This will allow the data flow to stream over the XML instead of loading the entire thing into a DOM.