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:
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
LVL 66
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

käµfm³d 👽Commented:
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
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
Nope.  I've always been able to hit the 'Generate XSD' file to have SSIS create it based on the XML file.
0
käµfm³d 👽Commented:
My fault. I completely glossed over that you clicked that button   : \
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

käµfm³d 👽Commented:
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
Vikas GargBusiness Intelligence DeveloperCommented:
Hello,

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

that will be better
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
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
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
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
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.