October 5, 2012 at 6:07 pm
Hi, I'm a relative noob at this - so please bare (bear?) with me.
I am retrieving XML data from a web service and I want to put this into a database for reporting purposes.
I currently use VS2008 to edit the SSIS package. I would use VS2010 or VS2012 but neither of them seem to let me use a URL in the "XML File Source" data flow task.
Anyway, when I put the URL into VS2008, I ask it to generate its own XSD - which it does. But what it does is split the XML up into what looks like tables. It also seems to generate its own ID field for each of the tables. I could probably use this as the unique key to join the 'tables' together. BUT, I can only download 12 months of data at a time - before it times out. So I created multiple instances of the XML File Source import - each specifying a different year in the URL. But it looks like the generated ID is reset to zero each time I run the XML File Source import - thus resulting in duplicate ID's. So, the self-generating ID's (if that's what they are) are no good to me.
I was looking at trying to manually modify the XSD...or use XSLT transformations....or importing the XML into SQL as is and dealing with it there - but my SQL isn't super dooper either.
Can anyone offer any advice please? Either specific to the issues mentioned above - or about my whole approach.
Much appreciated.
Regards,
Bryan
EDIT: SQL2008
October 5, 2012 at 6:51 pm
Going to try a merge transform as per http://blogs.msdn.com/b/mattm/archive/2007/12/11/using-xml-source.aspx
EDIT: Hmm proving to be a challenge using Merge Joins. It only accepts two inputs at a time and I have about 10 I want to combine. There must be an easier way. Perhaps I should put everything into staging tables then learn some SQL to join everything together, then update to the live tables, purging the staging tables afterwards?
October 21, 2012 at 5:31 pm
Using a Web Service Task that writes the results of the call to a file may enable you to get started using SSIS 2012 instead of staying in SSIS 2008. You could then use an XML Source in a Data Flow Task or the SQLXML Bulk Load tool to load the files to staging tables in a database before applying the data to the final destination.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 24, 2013 at 8:25 pm
January 14, 2016 at 4:18 am
Hello,
I have got this thread and Here I would like to answer according to this. I have a package of
SSIS XML Source[/url] , In which all the steps is discussed in well proper way. There are various feature of this package that will helpful to do.
1.Read XML data from any SOAP/REST API Web Service using methods such as GET/POST.
2.Read XML data from single or multiple XML files (use of wildcard allowed. e.g. c:\data\*.xml)
3.Support for Path expression to extract data from any level (e.g. Extract Orders nested under Customer Node).
4.Support for passing custom headers to SOAP or REST Web service
5.Support for looping through multiple files using wildcard pattern (e.g. *.xml).
6.REST API Paging support to loop through multiple requests (see)
7.Support for SQL Server 2005, 2008, 2012, 2014 (32 bit and 64 bit)
If you got trouble I have a video of sample work that How we just do . Kindly Have a look.
http://zappysys.com/products/ssis-powerpack/ssis-xml-source/
Hope It will be helpful.
🙂
January 14, 2016 at 8:33 am
noober (5/24/2013)
Instead of writing it to a file and then using an xml source...why not just do everything in a script component? You can call the web service, de-serialize the xml and add it to your data flow...I have a step by step example here: http://dennysjymbo.blogspot.com/2013/05/using-script-component-source-to.html
That is a tidy solution, to never let the XML hit disk.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply