I am calling the web service using the SSIS 2012 Web Service task. The XML returned by the HTTP call is very similar:
<s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/"> <s:Body xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <GetFooResponse xmlns="http://acme1234"> <GetFooResult> <Foo FOOTYPE_CODE="2" FOOTYPE="Premium" FOO_CODE="11" FOONAME="example" ACTIVE_INACTIVE="A"/> <Foo FOOTYPE_CODE="1" FOOTYPE="Standard" FOO_CODE="12" FOONAME="example2" ACTIVE_INACTIVE="A"/> </GetFooResult> </GetFooResponse> </s:Body> </s:Envelope>
But when I debug SSIS after storing the XML result in a variable or file on disk, I see that the saved XML is:
<?xml version="1.0" encoding="utf-16"?> <GetFooResponse xmlns="http://acme1234"> <Foo FOOTYPE_CODE="2" FOOTYPE="Premium" FOO_CODE="11" FOONAME="example" ACTIVE_INACTIVE="A"/> <Foo FOOTYPE_CODE="1" FOOTYPE="Standard" FOO_CODE="12" FOONAME="example2" ACTIVE_INACTIVE="A"/> </GetFooResponse>
I really don't understand how this is possible - how can SSIS remove an intermediate node? . I'm pretty good at removing external SOAP elements, but where did GetFooResult come from? As a result, my XSD is no longer valid; I canโt get a working SSIS package if I donโt fit XSD manually.
Any ideas how I can fix this, so the SSIS web service task results in the same XML that is extracted from the web service task?
source share