How to import XML Data in SSIS

The XML source is a powerful SSIS source that can use a local or remote (via HTTP or UNC) XML file as the source. This source component is a bit different from the OLE DB Source in its configuration.

In the below example, I am using sample "purchase order" and "customers and orders" XML files which can be downloaded from the MSDN site: http://msdn.microsoft.com/en-us/library/bb387034.aspx and http://msdn.microsoft.com/en-us/library/bb387025.aspxrespectively. You can generate XSD file using XML source component.

In this XML file you can noticed that, four level or nodes available.

XML source automatically identify and display the Nodes and its value and attributes.

Each tabular set can be loaded individually into its own table.  Thus, we actually can add multiple any kind of destinations, and split the data flow accordingly.

Cheers!
Uma