Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


XML from RESTful web service into SQL table(s) using SSIS


XML from RESTful web service into SQL table(s) using SSIS

Author
Message
bryan.gilberd
bryan.gilberd
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 24
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
bryan.gilberd
bryan.gilberd
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 24
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?
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8271 Visits: 14368
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
noober
noober
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 16
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: [url=http://dennysjymbo.blogspot.com/2013/05/using-script-component-source-to.html][/url]
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8271 Visits: 14368
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search