Wrox Programmer Forums
BOOK: Professional SQL Server 2005 Integration Services ISBN: 0-7645-8435-9
This is the forum to discuss the Wrox book Professional SQL Server 2005 Integration Services by Brian Knight, Allan Mitchell, Darren Green, Douglas Hinson, Kathi Kellenberger, Andy Leonard, Erik Veerman, Jason Gerard, Haidong Ji, Mike Murphy; ISBN: 9780764584350
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Professional SQL Server 2005 Integration Services ISBN: 0-7645-8435-9 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old July 7th, 2006, 01:23 PM
Registered User
Join Date: Jul 2006
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default SSIS :: Reading from XML source into Recordset

  In "Data Flow", I am attempting to use "XML Source" to read a XML document into a recordset and in "Control Flow", into a Foreach Loop Container ( foreach ADO enumerator ), where I map the attributes to DTS variables. SSIS allows you to specify the input columns by means of an index number. The problem is you don't know what index value to use for a particular input attribute. It does not seem to be in the same order as the what the XML input is. This is very troublesome. Ideally, you should be allowed to select the value based on attribute name.
  Has anyone seen this problem ? Any ideas ?
Old January 12th, 2016, 07:48 AM
Registered User
Join Date: Aug 2015
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default Reading from XML source

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

Similar Threads
Thread Thread Starter Forum Replies Last Post
SSIS XML as a destination? GethWho BOOK: Professional SQL Server 2005 Integration Services ISBN: 0-7645-8435-9 5 February 3rd, 2016 08:22 AM
SSIS Package:RawFile Source,FlatFile Destination Mit_2807 BOOK: Professional SQL Server 2005 Integration Services ISBN: 0-7645-8435-9 0 September 19th, 2008 04:01 AM
Connect a package of SSIS (dtsx) like data source Edwin2008 SQL Server DTS 1 January 7th, 2008 11:49 PM
Values from recordset lost after reading vanik.cz Classic ASP Databases 11 January 4th, 2008 07:36 AM
Hard (recordset field) reading fskilnik Dreamweaver (all versions) 10 November 28th, 2005 06:05 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.