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