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 use 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