Here I would like to mention some steps that How to start working with XML source
1.In BIDS/SSDT create new SSIS package
2.From the SSIS toolbox drag and drop Data Flow Task on the control flow designer surface.
3.Double click on the DataFlow task to see DataFlow designer surface.
4.From the SSIS toolbox drag and drop XML Source on the dataflow designer surface.
5.Double click XML Source
to configure it.
6.From the Access Mode dropdown select [File path or web Url] and paste the following Url for this example .
7.Now enter Path expression in Path textbox to extract only specific part of XML file as below ($.GeocodeResponse.result.address_component[*] will get content of address_component array attribute from XML document. address_component attribute is treated as array because appear more than once under same parent node, so we have to use[*] to indicate we want all records of that array)
8.Click preview to see our settings are OK.
9.Now one issue with XML parsing is how to determine which element(s) should be treated as an Array so expression engine can parse[*] type of expression correctly? For that you can type list of Elements names (comma separated) under Array Handling tab. In our case we want to treat type element as Array as below
10.Click OK to save settings.
11.From the SSIS toolbox drag and drop Trash Destination on the dataflow designer surface.
12.Now single click on the XML Source, once you see blue arrow from source ... connect it to Trash Destination.
We have also sample work with snapshort and one more video where task is discussed. If you got trouble You can follow us here