?
Solved

SSIS 2012:  How to connect to a SOAP file

Posted on 2014-08-15
8
Medium Priority
?
846 Views
Last Modified: 2016-02-26
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:
ssis-web-services-task-unable-to-infer-tAccording 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
0
Comment
Question by:Jim Horn
  • 4
  • 3
8 Comments
 
LVL 75

Assisted Solution

by:käµfm³d 👽
käµfm³d   👽 earned 1002 total points
ID: 40263049
Do you not have a schema for the XML? According to the error message, SSIS is trying to determine if the XML is valid according to its schema definition, which it cannot find. Do you have a .xsd file anywhere?
0
 
LVL 66

Author Comment

by:Jim Horn
ID: 40263069
Nope.  I've always been able to hit the 'Generate XSD' file to have SSIS create it based on the XML file.
0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 40263112
My fault. I completely glossed over that you clicked that button   : \
0
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
LVL 75

Assisted Solution

by:käµfm³d 👽
käµfm³d   👽 earned 1002 total points
ID: 40263127
If you have access to Visual Studio, then you might try using the XSD.exe utility that comes packaged with it. It has functionality that will attempt to infer a schema from an XML document. (Hopefully that's not what SSIS uses!) The syntax would be:

    xsd.exe xml_doc_name.xml
0
 
LVL 15

Assisted Solution

by:Vikas Garg
Vikas Garg earned 498 total points
ID: 40267229
Hello,

I think you can try WebService Task for consuming the SOAP service if it is remotely available .

that will be better
0
 
LVL 66

Author Comment

by:Jim Horn
ID: 40268336
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

0
 
LVL 66

Author Comment

by:Jim Horn
ID: 40268865
Perhaps a better question would be 'What are common ways to perform a Web Services Task on a SOAP file other than using SSIS?
0
 
LVL 66

Accepted Solution

by:
Jim Horn earned 0 total points
ID: 40317902
Found out .. the answer is a know issue of 'You can't do that' with SSIS and XML files with multiple namespaces.
I'll award points for the help, and close the question.
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question