Trying to pull many records from WebService using SSIS

  • E P Kelly

    SSC Veteran

    Points: 202

    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.

  • Crispin Proctor

    SSCoach

    Points: 18161

    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,
    Crispin


    I can't die, there are too many people who still have to meet me!

    It's not a bug, SQL just misunderstood me!

  • E P Kelly

    SSC Veteran

    Points: 202

    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...

  • Crispin Proctor

    SSCoach

    Points: 18161

    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,
    Crispin


    I can't die, there are too many people who still have to meet me!

    It's not a bug, SQL just misunderstood me!

  • E P Kelly

    SSC Veteran

    Points: 202

    Thank you for the assistance. It did end up being bad data. We finally located it and got the provider to correct/filter it. 😀

  • yohannn

    SSC Enthusiast

    Points: 109

    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 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply