Calling SOAP Web Service in SSIS (XML Source)

Introduction

In this article, you will learn how to call SOAP Web Service in SSIS. We will use drag and drop approach to consume SOAP Webservice using SSIS XML Source Connector. This same XML Connector can be used to consume data from local XML Files (wild card allowed e.g. *.xml) or you can consume XML stored in SSIS Variable. Great way to Parse XML Stored inside the database.

In this article, we will consume data from SSRS SOAP web service and load into SQL Server. You can apply a similar technique to call any web service.

Video Tutorial – Load SOAP Web Service data into SQL

Video Tutorial – Create XML and POST data to SOAP Web Service or REST API

Now lets look at examples of creating XML from multiple data sources and POST XML request to SOAP Web Service URL or any other XML Based REST API URL.

What is SOAP Web Service?

If you are new to SOAP Web Service sometimes referred as XML Web Service then please read some concept about SOAP Web service standard from this link

There are two important aspects in SOAP Web service.

1. Getting WSDL file or URL
2. Knowing exact Web Service URL

What is WSDL

In very simple term WSDL (often pronounced as whiz-dull) is nothing but a document which describes Service metadata (e.g. Functions you can call, Request parameters, response structure etc). Some service simply give you WSDL as xml file you can download on local machine and then analyze or sometimes you may get direct URL (e.g. http://api.mycompany.com/hr-soap-service/?wsdl )

What is Service URL

Service URL is actual API URL you can call to get data. When you make a request to SOAP service url make sure following things.

1. Use correct service URL (Its not same as WSDL url). If you are not sure about service URL contact API vendor or refer API documentation.
2. Each service call you have to supply function you want to call. This can be done in 3 different ways.
   1. Pass via SOAPAction HTTP Header
   2. Pass via Url parameter or Url Path ( http://myserver/service/function1  — http://myserver/service/?method=function1 )
   3. Pass via Request BODY
3. Find out how to pass credentials. It can be done via Basic Authorization or WSS Security Header in the Request body. Check this link to learn more

How to call SOAP API in SSIS (Summary)

To call SOAP API in SSIS you have to perform the following steps at a high level. Detailed steps are described in the next section

1. Download and Install SoapUI (It’s a Free tool to test SOAP / REST Services)
2. Import WSDL file or URL for your Service in SoapUI for testing
3. Add credentials for SOAP Web service (Basic Auth or WSS)
4. Create SOAP Request XML (Edit default parameters) and Click Play Button to test your request
   

   Create SOAP Request Body from WSDL (Using SoapUI tool)

5. If your test works in SoapUI then you ready to move to SSIS part
6. Copy Service URL, Request Body and Headers to SSIS (For Header see Raw Tab on SoapUI > Copy two lines SOAPAction:***** and  Content-Type:******)
   

   Get ContentType and SOAPAction Headers from SoapUI Raw tab

7. Use SSIS REST API Task or SSIS XML Source or SSIS Web API Destination to call SOAP API (Use correct API URL, Body and Headers)

See the next section for detailed steps for calling SOAP web service in SSIS.

Step-By-Step : Calling SOAP Web Service in SSIS

In this section you will learn how to call SOAP Web service in SSIS without any type of coding. In few clicks you will be able to load data from XML SOAP Service to SQL Server. For demo purpose we will call SSRS Web service but you can pick any available SOAP Service.

1. Download and Install SSIS PowerPack FREE Trial from this link
2. Now Download SoapUI (Its popular free third-party tool by other vendor). SoapUI can help you to generate XML Request BODY easily by using WSDL file. You can also test your service by supplying parameters.
3. Now find out your SOAP Service WSDL URL. It can be URL or locally stored XML file. WSDL is standard to describes SOAP Service (e.g. Available functions, request/response format). For SSRS WSDL is located usually at below location. Sometime WSDL URL is not correctly downloaded in SoapUI in that case use web browser to download WSDL and save to local disk.
   
   http://YourReportServer/reportserver/ReportService2010.asmx?wsdl

   1	http://YourReportServer/reportserver/ReportService2010.asmx?wsdl

4. Now launch SoapUI. Click on File menu and Select New SOAP Project
5. Enter your WSDL file path or URL
6. Click OK to create Project. This will create some dummy requests for you. If you want to see all available parameter in your Request you can create New Request by right click on Method node and select New Request. When prompted to create optional parameters then click yes.
   

   Create new SoapUI project to generate / test Soap Requests

7. Click on Request node as below and it will show you XML code for your SOAP request. You can edit parameters as needed. We will use this code in our XML Source to build SOAP Request. Some time your SOAP Service may require UserID/Password in SOAP Body or via Authorization Header like this one. If you passing credentials Authorization header then you can use ZS-HTTP connection (on XML Source UI check Use Credentials option and create new connection)
   

   SOAP Request XML

8. Create new SSIS Project and add new package.
9. Open Package. Go to control flow. Drag and drop Data Flow task from SSIS Toolbox
10. Go to Data flow designer. Drag and drop ZS XML Source Component
11. While making SOAP Request you have to configure following items in your XML Source.
    • URL
    • Request Method (e.g. POST)
    • Headers
    • Request Body (for request SOAP XML)
12. Double click XML Source to configure it.
    1. Set webservice URL
    2. Set request method to POST
    3. In the headers grid click Raw Edit to add 2 more headers as below.
       
       If you not sure about SOAP Action then find out from SoapUI > Raw Tab of Request Panel. Headers are only generated if you click the green Play button in SoapUI. Copy following two headers from SoapUI Raw tab and paste under SSIS XML Source > Headers Grid > Raw Edit. 
       XHTML

       Content-Type: text/xml;charset=UTF-8 SOAPAction: "http://some-url-of-your-api-call"

       1 2	Content-Type: text/xml;charset=UTF-8 SOAPAction: "http://some-url-of-your-api-call"

       SOAPAction URL is also listed under the following place in SoapUI.
       

       How to find SOAPAction Header value using SoapUI tool

13. Now click on Edit option next to Body Textbox. You can Copy sample Request XML generated in SoapUI and paste in Request Data (See below screenshot). You have to make sure to include all required fields in your XML Request (You may create new XML request in SoapUI with include Optional fields Turned ON to see all parameters).
14. XML Source supports using Placeholders in request URL, body and headers so its great way to make things dynamic. If you have need to use placeholders then you can use {Insert Variable} option like below screenshot and add variable anywhere in the text.
15. You can also edit Filter to control how data is returned. If you have Nested nodes in XML response you can select data node which you care about. Click select Filter button to navigate through hierarchy.
    

    Filter SOAP Web Service – XML response data

16. Un-check include parent option if you don’t care about getting parent attributes.
17. Your final UI may look like below after setting Url, Method, Body and Headers.
    

    Calling SOAP Web Service in SSIS using XML Source Connector

18.  On Array handling tab you can type all the element names you know for sure that going to have one or more records. For example if your response looks like below you can enter a:row in the possible array fields on Array Handling tab. That way if you have just one record rather than multiple records Filter expression like this wont fail ( $.a:response.a:data.a:row[*] ) .
    
    <a:response> <a:data> <a:row>....</a:row> <a:row>....</a:row> </a:data> </a:response>

    1 2 3 4 5 6

    <a:response> <a:data> <a:row>....</a:row> <a:row>....</a:row> </a:data> </a:response>

     
19. Now once all settings entered. Click Preview.
    

    Set Filter and Preview SOAP Web Service Response in Table Format

Creating SOAP Request with Optional Parameters

Sometimes if you create SOAP request you may not see optional parameters. Here is how you can create SOAP Request with optional parameters.

Create SOAP Request XML (With Optional Parameters)

Load XML Data into SQL Server

Once you configure XML source now you can connect source to target such as SQL Server, MySQL, Oracle, Flat File etc. On target you can map various source columns to SQL Server Table columns.

SSIS SOAP Web Service Call – Loading XML data into SQL Server Table (or MySQL, Oracle, FlatFile)

Sample SOAP Webservices for Testing (FREE Public API)

If you like to test few more scenarios and you do not have SOAP service credentials then no worries. Try this link. It lists many FREE SOAP services which can be used for testing purpose without real credentials.

Example Request:

Here is sample SOAP API call to convert number into word (i.e. input=500, output=five hundred).
Enter Method, URL, Headers and Body like below on ZappySys UI

Response

SOAP API Pagination (Loop through large dataset)

By default your SOAP API may not return full dataset if you have too many records to loop through then you can use Pagination techniques discussed in the following articles.

How to get data from Google AdWords using SSIS

Various Paging methods for REST API

Sending complex SOAP Request (Nested Parent-Child Array)

If you have to call more complex SOAP request which requires Parent-child structure (Nested Array) then you cant use Template Transform. In that case, check this article for real-world example.

Create nested XML for SOAP Request – Multiple inputs (POST data to API using SSIS Web API Destination)

Extract single XML node from SOAP Response

There will be a time when you need to extract just one value out of your response and save into SSIS variable / use it later on. If that’s the case then refer to this article.

Conclusion

Loading data from SOAP Api can be tricky and most of the time it requires use of SDKs or programming languages such as Java, C#, Python, Ruby etc. SSIS PowerPack comes with many RESTful API connectors including SOAP/XML Source and JSON Source. Try SSIS PowerPack yourself and discover many features not discussed in this article.