March 24, 2009 at 8:53 pm
As part of an SSIS package I have a task where I need to create a temp table with data from a web service.
I have successfull created a Web Service Task which gets the data and stores it in an xml file. I chose an xml file because i believe the string variable has a size limit which i will overrun.
However my xml source is unable to read the records in the xml file. I have searched on this topic and it appears to be a problem only when the web service returns a recordset or diffgram element in the xml script. The xml source sees zero records.
So my question(s) are;
1. is there another way for the XML source to read the xml file that will work ?
2. is there another way for SSIS to read a web service and export the data into a table ?
3. Can i place the data returned from the web service in a variable like an object, rather than a file. If so how do i extract it (code example please)
and get it into my temp table.
From my readings i find it hard to believe that SSIS XML source is unable to read the most common xml format returned by web services ie a recordset.
Any help appreciated.
March 26, 2009 at 6:19 pm
Ok. After 2 days i have figured out why this has caused me and many others out there such a problem.
The "xml source" control can only read xml files that contain simple types. If your web service spits out a dataset (diffgram element) or uses even an array of strings then the "xml source" will not work.
I was lucky in that I could also rewrite the web service, which I did. I dropped all the string array elements and where neccesary i created collections of objects composes of strings. These can be read by SSIS "XML Source" control.
I think you'll agree that this is pretty crappy functionality from Microsoft. Web services created in .Net which spit out dataset/diffgrams and other complex datatypes like string arrays are readable by other .net programs. Why the SSIS "XML Source" can't read, when it too is a microsoft product shows that the left hand of microsoft doesn't know what the right hand is doing.
Happy developing
March 26, 2009 at 7:23 pm
Feeling bitter after two days of pain? 🙂 We've all been there and sympathise, I'm sure ...
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply