Sideout1972 (9/28/2015)
XML structure that goes more than 2 levels deep performs horribly with SSIS during the development cycle. Drop-downs where you are picking a node could take minutes, and even hours to load if the XML has a complex structure.The only reasonable performance comes with using openrowset, XPATH, etc.
Using SSIS best practices isn't always the best performing way of accomplishing a task.
Amen to that. Same goes for XML in general, IMHO. Between the tag bloat and the other problems associated with it, it's just easier to go with a nice, properly normalized, properly delimited flat file.
--Jeff Moden
Change is inevitable... Change for the better is not.