SSIS XMl Processing

Question

For my work, I am doing a very large import of (products) data. We recently started using SSIS and it works better than custom .net import tools. After 3 more projects, we found out that it is more efficient to use a script script with C # xpath and sql instructions, and then use an XML source and combine the joins into a data stream.

Data Flow Issues

• Sometimes xml cannot read from a variable
• It depends too much on a fixed XML definition (you need to know xsd)
• When changing definitions, the whole must be changed.
• For processing a specific type; UINT and INT are not the same
• It is not easy to debug and go through C # code

Can anyone share their SSIS data import stories?

Answer 1

I work "completely" with XML in SQL Server, and basically it is an import function in some of my projects. I do not use the SSIS XML source due to the limitations of the XML structure. As an example, it is impossible to import data from Excel XML, where not only the data, but also the column names are values. To prepare this type of import, I use the TSQL code. Perhaps you can use third-party components for this example, but not all customers want to pay for it.))

Answer 2

You can try experimenting with some of the third-party components in CodeProject, or if your C # / VB skills are busy for this, then consider folding up your sleeves and writing the original script component to destroy the xml using the SAX parser. This will allow the flow of data through XML instead of loading the entire object in the DOM.