Search code examples
web-servicesssis-2012

Why is this web service's XML response causing an error in the SSIS web service task?


I'm using SSIS 2012 to create a package which will retrieve data from a web service and load a database. I've already successfully created a number of web service tasks in this package calling a number of other methods on this web service, but something is going wrong with one of them.

The GetDeviceInfo method accepts the following parameters:

  1. certificate - Received after calling the WS login method
  2. accountId - Account which the device exists in
  3. cmuId - The serial number of a specific device

When calling the method using an arbitrary cmuId which I know does not exist (1234567 in this example), the XML response, which says that no records were found, is correctly returned as follows:

<?xml version="1.0" encoding="utf-16"?>
<ResponseModel xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"     xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <message xsi:nil="true"     xmlns="http://schemas.datacontract.org/2004/07/CellocatorPlusModels" />
  <state xmlns="http://schemas.datacontract.org/2004/07/CellocatorPlusModels">valueNotFound</state>
  <timestamp xmlns="http://schemas.datacontract.org/2004/07/CellocatorPlusModels">6.36186059963802E+17</timestamp>
</ResponseModel>

As soon as a valid value for cmuId is entered, however, SSIS throws an error:

SSIS package "<redacted>\getData.dtsx" starting.
Error: 0xC002F304 at Web Service Task, Web Service Task: An error occurred with the following error message: "Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebserviceTaskException: Could not execute the Web method. The error is: There was an error generating the XML document..
   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()".
Task failed: Web Service Task
SSIS package "<redacted>\getData.dtsx" finished: Success.

The web service task fails and the response is not written to the output file.

Calling the web service from a browser, using the same parameters, returns the following, which is exactly what I'm expecting:

<ResponseModel xmlns="http://schemas.datacontract.org/2004/07/CellocatorPlusModels" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
  <message i:type="ExtendedUnitInfoModel">
    <Account><..></Account>
    <AccountId i:nil="true"/>
    <AssemblyDate>05/13/2015</AssemblyDate>
    <CMUID>1169817</CMUID>
    <CurrentFWVersion>331</CurrentFWVersion>
    <FWStatus>On Wait</FWStatus>
    <FirstFWVersion i:nil="true"/>
    <FirstPLVersion i:nil="true"/>
    <FirstStatusDate>05/13/2015</FirstStatusDate>
    <GroupID>22493</GroupID>
    <GroupName>CR300B</GroupName>
    <Modem>CR300B GE864</Modem>
    <PLName i:nil="true"/>
    <ProgStatus>Normal Operation</ProgStatus>
    <Prov></Prov>
    <ProviderId>5186</ProviderId>
    <PurchaseOrder>SO28906.4</PurchaseOrder>
    <SIMNumber/>
    <SerialId/>
    <ShipmentDate>06/07/2015</ShipmentDate>
    <ShipmentTrackingNumber>8577</ShipmentTrackingNumber>
    <TesterName>Arcam3</TesterName>
    <TestingDate>05/14/2015</TestingDate>
    <WarrantyExpDate>06/08/2016</WarrantyExpDate>
  </message>
<state>success</state>
<timestamp>6.3618603925336154E+17</timestamp>
</ResponseModel>

I've tried creating a standalone package containing only this web service task for troubleshooting, with the same results.

Since I can call other methods on this WS perfectly OK, the task runs properly when I specify a non-existent device and I can correctly write to the output file under these circumstances I can only conclude that something is malformed in this response or I'm missing something very silly.

Any ideas? Thanks in advance.


Solution

  • Coming back to this question after several years, I have concluded that this issue was due to some idiosyncrasy in SSIS processing complex XML using the web service task component.

    The solution that ultimately worked for me was implementing the operation in an execute code component using C#. Here's the basic template (only parsing a single field for demonstration).

    public void PreExecute()
    {
        // Load the XML response into an XDocument object
        XDocument doc = XDocument.Parse(Dts.Variables["User::XmlResponse"].Value.ToString());
    
        // Select the message element
        XElement messageElement = doc.Root.Element("message");
    
        // Select the CMUID element
        XElement cmuidElement = messageElement.Element("CMUID");
    
        // Extract the value of the CMUID element
        string cmuid = cmuidElement.Value;
    
        // Store the CMUID value in a variable
        Dts.Variables["User::CmuId"].Value = cmuid;
    }
    

    Perhaps this will help someone else struggling with a similar issue.