August 12, 2008 at 3:19 pm
Has anyone pulled a large number of small records from a WebService? Can you work with the data without writing it to a disk file?
Right now I have a web service that has 2 methods, method 1 I can pull the data from and write it to a file fine (7475 records) . But I really want the data in method 2, that crashes when I read it to a file or variable. (Do I have other choices?) I do not know exactly how many records method 2 is pulling, I expect around 33000.
I have a HTTP Connection being used by a WebService Task in an SSIS package. The provider of the WebService swears there is no error in the XML...
But I get:
, Web Service Task: An error occurred with the following error message:
"Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebserviceTaskException: The Web Service threw an error during method execution.
The error is: There is an error in XML document (1, 3502230)..
at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebMethodInvokerProxy.InvokeMethod(DTSWebMethodInfo methodInfo, String serviceName, Object connection)
at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebServiceTaskUtil.Invoke(DTSWebMethodInfo methodInfo, String serviceName, Object connection, VariableDispenser taskVariableDispenser)
at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebServiceTask.executeThread()".
And nothing in the variable or file.
August 12, 2008 at 11:55 pm
I suspect there is an error in the service.
The error is: There is an error in XML document (1, 3502230)..
Does this work with other apps? Can you call it through IE?
What is the total size of the records? A variable can only hold 2GB.
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
August 13, 2008 at 6:45 am
The web service is not set up to be run through IE, so I can't get the results that way. I don't have any other applications that access secure web services so I have not attempted that yet.
Under the covers does SQL read the XML into a variable before putting it into a file? Could it be more than 2 GB and that is why it fails? Each record is 17 fields of average less than 25 bytes per field, so each record is not big but 33,000 of them...
August 13, 2008 at 7:05 am
I have tried using the webservice task before and did actually find it rather flaky when talking to cold fusion. I ended up writing my own custom task to deal with it.
You have to find a way to get at what is returned and see what it says. It does look like a malformed xml issue but you can't say for sure until you see...
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
August 18, 2008 at 10:54 am
Thank you for the assistance. It did end up being bad data. We finally located it and got the provider to correct/filter it. 😀
June 23, 2020 at 6:28 pm
Ok, now its 2020 and SSIS has matured quite a bit. I am bulk loading data using a web service task to call a SOAP web service. One method call works but the second generates the "There is an error in XML document" error. I think this error is generated because the response xml is "snipped" and the result is a poorly formed xml document.
I believe this because I wrote a console application where the response was fully received, and it contained large number of objects (44k+). To achieve this, I had to set maxReceivedMessageSize to 2GB. Inspection of the response data passes and there is no reason it should not work in SSIS.
In SSIS, I trimmed down the requested data size to around 33k objects and the web service task completed as expected, implying the response data and SSIS configuration is good. Although I have several less elegant solutions, hopefully there is a setting like "maxReceivedMessageSize" in the SSIS the http connection or web service task containers. Does anybody have an answer for this issue?
PS- I am not above making mistakes and I have thick skin. Also, I can ask for modifications to the web service modified as long as requests are within reason
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply