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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy