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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Data Warehousing » Integration Services » XML from RESTful web service into SQL...

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

Rate Topic

Display Mode

Topic Options

Author

Message

bryan.gilberd

Posted Friday, October 05, 2012 6:07 PM

Forum Newbie

Group: General Forum Members
Last Login: Friday, October 05, 2012 9:46 PM
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

Post #1369354

bryan.gilberd

Posted Friday, October 05, 2012 6:51 PM

Forum Newbie

Group: General Forum Members
Last Login: Friday, October 05, 2012 9:46 PM
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?

Post #1369357

opc.three

Posted Sunday, October 21, 2012 5:31 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:14 PM
Points: 6,706, Visits: 11,738

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1375217

« Prev Topic | Next Topic »

Permissions