asked on

SSIS 2012: How to connect to a SOAP file

Hi all

( Warning: SQL and SSIS expert, XML N00b )

Question: Does anyone know how in SSIS 2012 to connect to a SOAP file source?

When I execute a Web Services Call with SOAPUI I get the following file (first 20 lines)

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
   <soapenv:Body>
      <ns1:queryRecordsResponse soapenv:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/" xmlns:ns1="http://source.trax.aero">
         <queryRecordsReturn xsi:type="ns2:Defect_Report_Outgoing_Array_Element" xmlns:ns2="http://defect_report.techrecords.castor.trax.aero">
            <defect_Report_Outgoing_Element xsi:type="ns2:Defect_Report_Outgoing_Element">
               <AC xsi:type="soapenc:string" xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/">714</AC>
               <AC_Master xsi:type="ns2:AC_Master">
                  <ac xsi:type="soapenc:string" xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/">714</ac>
                  <ac_Block_Hours xsi:type="xsd:int">5612</ac_Block_Hours>
                  <ac_Block_Minutes xsi:type="xsd:int">26</ac_Block_Minutes>
                  <ac_Brake_Cycles xsi:type="soapenc:decimal" xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/">17969</ac_Brake_Cycles>
                  <ac_Component_Warranty_Cycles xsi:type="xsd:int">0</ac_Component_Warranty_Cycles>
                  <ac_Component_Warranty_Days xsi:type="xsd:int">0</ac_Component_Warranty_Days>
                  <ac_Component_Warranty_Hours xsi:type="xsd:int">0</ac_Component_Warranty_Hours>
                  <ac_Component_Warranty_Vendor xsi:type="soapenc:string" xsi:nil="true" xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/"/>
                  <ac_Cycles xsi:type="soapenc:decimal" xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/">17969</ac_Cycles>
                  <ac_Defect_Item_Out_Of_Service xsi:type="xsd:int">0</ac_Defect_Item_Out_Of_Service>
                  <ac_Defect_Out_Of_Serive xsi:type="soapenc:string" xsi:nil="true" xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/"/>
                  <ac_Defect_Type_Out_Service xsi:type="soapenc:string" xsi:nil="true" xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/"/>
                  <ac_Description xsi:type="soapenc:string" xsi:nil="true" xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/"/>

Open in new window

When I use a Web Services Task to make this call I receive the below 'There is an error in XML document (1, 23426)' error message, which doesn't tell me anything useful

[Web Service Task] Error: 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, 23426)..
   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()".

Open in new window

Then when I save that file as .xml, then create an SSIS XML Task, connect to that file XML file and click the Generate XSD... button, I receive the following message:

According to this article (scroll down) this is a known limitation of SSIS 2012.

So my options (see above XML Noob comment) appear to be...

Parse the file so that it can be consumed by an XML Task

Create a big honkin' script task to consume the SOAP file

Points for any useful comments, to include splitting this into multiple questions.

Thanks in advance.
Jim

SOLUTION

kaufmed

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jim Horn

ASKER

Nope. I've always been able to hit the 'Generate XSD' file to have SSIS create it based on the XML file.

kaufmed

My fault. I completely glossed over that you clicked that button : \

SOLUTION

kaufmed

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Vikas Garg

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jim Horn

ASKER

Vikas - Getting the below message when using a Web Services Tasks. I'll add this to the original question.

[Web Service Task] Error: 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, 23426)..
   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()".

Open in new window

Jim Horn

ASKER

Perhaps a better question would be 'What are common ways to perform a Web Services Task on a SOAP file other than using SSIS?

ASKER CERTIFIED SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial