Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

XML from RESTful web service into SQL table(s) using SSIS Expand / Collapse
Author
Message
Posted Friday, October 05, 2012 6:07 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Friday, October 05, 2012 6:51 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Sunday, October 21, 2012 5:31 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 9:25 AM
Points: 7,070, Visits: 12,523
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
Posted Friday, May 24, 2013 8:25 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, April 05, 2014 11:05 PM
Points: 1, Visits: 15
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]
Post #1456754
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse