September 12, 2007 at 8:44 am
Hi
I am trying to import XML data from a web service.
I need to pass variables to the webpage which produces a new XML document which then needs to be imported.
So in a stored procedure I would cycle through types, make a call to the website with the type
ke.test.com\example\?type=1 ----> This displays an XML document
I import this data into a SQL table
and then go on to
ke.test.com\example\?type=2
ke.test.com\example\?type=3 etc...
I have been looking into using CLR but I havent found that much documentation
stuck! help
thanks
September 13, 2007 at 4:22 am
This is probably more a job for SSIS than for stored procs.
Calling a web service in SSIS is pretty easy (there's a task for it), and you should be able to do a loop over the table that contains the types you need to hit.
Not sure if you need to call this on a schedule, or it's something the front-end app would kick off. If it's the latter, may be a bit tougher to do with SSIS.
September 13, 2007 at 4:46 am
Hi
I tried doing it in SSIS but I had some issues
Firstly its not a proper web service so the web service task would fail as there is no WSDL
I thought I could then do it with the XML Source in Data Flow, inserting a user variable of the URL into the XML location field.
SSIS didnt like the idea of a variable in the XML location. I think column mapping to the destination is not possible.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply