{"id":620,"date":"2016-06-23T13:51:11","date_gmt":"2016-06-23T13:51:11","guid":{"rendered":"http:\/\/zappysys.com\/blog\/?p=620"},"modified":"2020-10-07T16:35:08","modified_gmt":"2020-10-07T16:35:08","slug":"calling-soap-web-service-in-ssis-xml-source","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/calling-soap-web-service-in-ssis-xml-source\/","title":{"rendered":"Calling SOAP Web Service in SSIS (XML Source)"},"content":{"rendered":"<h2>Introduction<\/h2>\n<p>In this article, you will learn <em>how to call SOAP Web Service in SSIS<\/em>.\u00a0We will use drag and\u00a0drop approach to consume SOAP Webservice using <a href=\"\/\/zappysys.com\/products\/ssis-powerpack\/ssis-xml-source\/\" target=\"_blank\" rel=\"noopener\">SSIS XML Source Connector<\/a>. 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.<\/p>\n<p>In this article, we will consume data from SSRS <em>SOAP web service and load into SQL Server<\/em>. You can apply a similar technique to call any web service.<\/p>\n<h3>Video Tutorial &#8211; Load SOAP Web Service data into SQL<\/h3>\n<div class=\"lyte-wrapper fourthree\" style=\"width:480px;max-width:100%;margin:5px auto;\"><div class=\"lyMe qsa_\\&amp;showinfo\\=0\" id=\"WYL_d_x5bgGjg0Y\"><div id=\"lyte_d_x5bgGjg0Y\" data-src=\"https:\/\/zappysys.com\/blog\/wp-content\/plugins\/wp-youtube-lyte\/lyteCache.php?origThumbUrl=%2F%2Fi.ytimg.com%2Fvi%2Fd_x5bgGjg0Y%2Fhqdefault.jpg\" class=\"pL\"><div class=\"tC hidden\"><div class=\"tT\"><\/div><\/div><div class=\"play\"><\/div><div class=\"ctrl\"><div class=\"Lctrl\"><\/div><div class=\"Rctrl\"><\/div><\/div><\/div><noscript><a href=\"https:\/\/youtu.be\/d_x5bgGjg0Y\" rel=\"nofollow\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/zappysys.com\/blog\/wp-content\/plugins\/wp-youtube-lyte\/lyteCache.php?origThumbUrl=https%3A%2F%2Fi.ytimg.com%2Fvi%2Fd_x5bgGjg0Y%2F0.jpg\" alt=\"\" width=\"480\" height=\"340\" \/><br \/>Watch this video on YouTube<\/a><\/noscript><\/div><\/div><div class=\"lL\" style=\"max-width:100%;width:480px;margin:5px auto;\"><\/div><\/p>\n<h3>Video Tutorial &#8211; Create XML and POST data to SOAP Web Service or REST API<\/h3>\n<p>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.<br \/>\n<div class=\"lyte-wrapper fourthree\" style=\"width:480px;max-width:100%;margin:5px auto;\"><div class=\"lyMe\" id=\"WYL_TjTexGzBF5g\"><div id=\"lyte_TjTexGzBF5g\" data-src=\"https:\/\/zappysys.com\/blog\/wp-content\/plugins\/wp-youtube-lyte\/lyteCache.php?origThumbUrl=%2F%2Fi.ytimg.com%2Fvi%2FTjTexGzBF5g%2Fhqdefault.jpg\" class=\"pL\"><div class=\"tC\"><div class=\"tT\"><\/div><\/div><div class=\"play\"><\/div><div class=\"ctrl\"><div class=\"Lctrl\"><\/div><div class=\"Rctrl\"><\/div><\/div><\/div><noscript><a href=\"https:\/\/youtu.be\/TjTexGzBF5g\" rel=\"nofollow\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/zappysys.com\/blog\/wp-content\/plugins\/wp-youtube-lyte\/lyteCache.php?origThumbUrl=https%3A%2F%2Fi.ytimg.com%2Fvi%2FTjTexGzBF5g%2F0.jpg\" alt=\"\" width=\"480\" height=\"340\" \/><br \/>Watch this video on YouTube<\/a><\/noscript><\/div><\/div><div class=\"lL\" style=\"max-width:100%;width:480px;margin:5px auto;\"><\/div><\/p>\n<h3>What is SOAP Web Service?<\/h3>\n<p>If you are new to SOAP Web Service sometimes referred as XML Web Service then please read some concept\u00a0about\u00a0SOAP\u00a0Web service standard <a href=\"https:\/\/msdn.microsoft.com\/en-us\/library\/ms996507.aspx?f=255&amp;MSPPError=-2147217396\" target=\"_blank\" rel=\"noopener\">from this link<\/a><\/p>\n<p>There are two important aspects in SOAP Web service.<\/p>\n<ol>\n<li>Getting WSDL file or URL<\/li>\n<li>Knowing exact Web Service URL<\/li>\n<\/ol>\n<h3>What is WSDL<\/h3>\n<p>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\u00a0analyze or sometimes you may get direct URL (e.g. http:\/\/api.mycompany.com\/hr-soap-service\/?wsdl )<\/p>\n<h3>What is Service URL<\/h3>\n<p>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.<\/p>\n<ol>\n<li>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.<\/li>\n<li>Each service call you have to supply function you want to call. This can be done in 3 different ways.\n<ol>\n<li>Pass via SOAPAction HTTP Header<\/li>\n<li>Pass via Url parameter or Url Path ( http:\/\/myserver\/service\/function1\u00a0 &#8212; http:\/\/myserver\/service\/?method=function1\u00a0)<\/li>\n<li>Pass via Request BODY<\/li>\n<\/ol>\n<\/li>\n<li>Find out how to pass credentials. It can be done via Basic Authorization or WSS Security Header in the Request body. <a href=\"\/\/zappysys.com\/blog\/get-data-from-workday-in-ssis-using-soap-or-rest-api\/\" target=\"_blank\" rel=\"noopener\">Check this link to learn more<\/a><\/li>\n<\/ol>\n<h2>How to call SOAP API in SSIS (Summary)<\/h2>\n<p>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<\/p>\n<ol>\n<li>Download and Install <a href=\"https:\/\/www.soapui.org\/downloads\/latest-release.html\" target=\"_blank\" rel=\"noopener\">SoapUI<\/a> (It&#8217;s a Free tool to test SOAP \/ REST Services)<\/li>\n<li>Import WSDL file or URL for your Service in SoapUI for testing<\/li>\n<li>Add credentials for SOAP Web service (Basic Auth or WSS)<\/li>\n<li>Create SOAP Request XML (Edit default parameters) and <strong>Click Play Button <\/strong>to test your request\n<div id=\"attachment_2543\" style=\"width: 745px\" class=\"wp-caption alignnone\"><a href=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/01\/create-workday-soap-request-using-soapui-api-xml-body.png\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-2543\" class=\"size-full wp-image-2543\" src=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/01\/create-workday-soap-request-using-soapui-api-xml-body.png\" alt=\"Create SOAP Request Body from WSDL (Using SoapUI tool)\" width=\"735\" height=\"451\" srcset=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/01\/create-workday-soap-request-using-soapui-api-xml-body.png 735w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/01\/create-workday-soap-request-using-soapui-api-xml-body-300x184.png 300w\" sizes=\"(max-width: 735px) 100vw, 735px\" \/><\/a><p id=\"caption-attachment-2543\" class=\"wp-caption-text\">Create SOAP Request Body from WSDL (Using SoapUI tool)<\/p><\/div><\/li>\n<li>If your test works in SoapUI then you ready to move to SSIS part<\/li>\n<li>Copy Service URL, Request Body and Headers to SSIS (For Header see <strong>Raw<\/strong>\u00a0Tab on SoapUI &gt; Copy two lines\u00a0<strong>SOAPAction:*****<\/strong>\u00a0and\u00a0 <strong>Content-Type:******<\/strong>)\n<div id=\"attachment_7355\" style=\"width: 638px\" class=\"wp-caption alignnone\"><a href=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/06\/soapui-get-contenttype-soapaction-raw-tab.png\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-7355\" class=\"size-full wp-image-7355\" src=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/06\/soapui-get-contenttype-soapaction-raw-tab.png\" alt=\"Get ContentType and SOAPAction Headers from SoapUI Raw tab\" width=\"628\" height=\"457\" srcset=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/06\/soapui-get-contenttype-soapaction-raw-tab.png 628w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/06\/soapui-get-contenttype-soapaction-raw-tab-300x218.png 300w\" sizes=\"(max-width: 628px) 100vw, 628px\" \/><\/a><p id=\"caption-attachment-7355\" class=\"wp-caption-text\">Get ContentType and SOAPAction Headers from SoapUI Raw tab<\/p><\/div><\/li>\n<li>Use <a href=\"https:\/\/zappysys.com\/products\/ssis-powerpack\/ssis-web-api-destination-connector\/\" target=\"_blank\" rel=\"noopener\">SSIS REST API Task<\/a> or <a href=\"\/\/zappysys.com\/products\/ssis-powerpack\/ssis-xml-source\/\" target=\"_blank\" rel=\"noopener\">SSIS XML Source<\/a> or <a href=\"https:\/\/zappysys.com\/products\/ssis-powerpack\/ssis-web-api-destination-connector\/\" target=\"_blank\" rel=\"noopener\">SSIS Web API Destination<\/a> to call SOAP API (Use correct API URL, Body and Headers)<\/li>\n<\/ol>\n<p>See the next section for detailed steps for calling SOAP web service in SSIS.<\/p>\n<h2>Step-By-Step : Calling SOAP Web Service in SSIS<\/h2>\n<p>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 <em>load data from XML SOAP Service to SQL Server<\/em>. For demo purpose we will call SSRS Web service but you can pick any available SOAP Service.<\/p>\n<ol>\n<li>Download and Install <a href=\"\/\/zappysys.com\/products\/ssis-powerpack\/\" target=\"_blank\" rel=\"noopener\">SSIS PowerPack FREE Trial<\/a> from <a href=\"\/\/zappysys.com\/products\/ssis-powerpack\/download\/\" target=\"_blank\" rel=\"noopener\">this link<\/a><\/li>\n<li>Now <a href=\"https:\/\/www.soapui.org\/downloads\/latest-release.html\" target=\"_blank\" rel=\"noopener\">Download SoapUI<\/a> (Its popular free third-party tool by <strong>other vendor<\/strong>). SoapUI can help you to generate XML Request BODY easily by using WSDL file. You can also test your service by supplying parameters.<\/li>\n<li>Now find out your SOAP Service WSDL URL. It can be URL or locally stored\u00a0XML 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.\n<pre class=\"lang:default highlight:0 decode:true\">http:\/\/YourReportServer\/reportserver\/ReportService2010.asmx?wsdl<\/pre>\n<\/li>\n<li>Now launch SoapUI. Click on File menu and Select New SOAP Project<\/li>\n<li>Enter your WSDL file path or URL<\/li>\n<li>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.\n<div id=\"attachment_641\" style=\"width: 615px\" class=\"wp-caption alignnone\"><a href=\"\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/06\/create-new-project-soapui.png\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-641\" class=\"size-full wp-image-641\" src=\"\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/06\/create-new-project-soapui.png\" alt=\"Create new SoapUI project to generate \/ test Soap Requests\" width=\"605\" height=\"428\" srcset=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/06\/create-new-project-soapui.png 605w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/06\/create-new-project-soapui-300x212.png 300w\" sizes=\"(max-width: 605px) 100vw, 605px\" \/><\/a><p id=\"caption-attachment-641\" class=\"wp-caption-text\">Create new SoapUI project to generate \/ test Soap Requests<\/p><\/div><\/li>\n<li>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\u00a0<a href=\"https:\/\/www.soapui.org\/soap-and-wsdl\/authenticating-soap-requests.html\" target=\"_blank\" rel=\"noopener\">like this one<\/a>. If you passing\u00a0credentials Authorization header then you can use ZS-HTTP connection (on XML Source UI check Use Credentials option and create new connection)\n<div id=\"attachment_642\" style=\"width: 783px\" class=\"wp-caption alignnone\"><a href=\"\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/06\/soap-generate-soap-xml-request-for-testing.png\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-642\" class=\"size-full wp-image-642\" src=\"\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/06\/soap-generate-soap-xml-request-for-testing.png\" alt=\"SOAP Request XML\" width=\"773\" height=\"372\" srcset=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/06\/soap-generate-soap-xml-request-for-testing.png 773w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/06\/soap-generate-soap-xml-request-for-testing-300x144.png 300w\" sizes=\"(max-width: 773px) 100vw, 773px\" \/><\/a><p id=\"caption-attachment-642\" class=\"wp-caption-text\">SOAP Request XML<\/p><\/div><\/li>\n<li>Create new SSIS Project and add new package.<\/li>\n<li>Open Package. Go to control flow. Drag and drop Data Flow task from SSIS Toolbox<\/li>\n<li>Go to Data flow designer. Drag and drop ZS XML Source Component<\/li>\n<li>While making SOAP Request you have to configure following items in your XML Source.\n<ul>\n<li>URL<\/li>\n<li>Request Method (e.g. POST)<\/li>\n<li>Headers<\/li>\n<li>Request Body (for request SOAP XML)<\/li>\n<\/ul>\n<\/li>\n<li>Double click XML Source to configure it.\n<ol>\n<li>Set webservice URL<\/li>\n<li>Set request method to POST<\/li>\n<li>In the headers grid\u00a0click Raw Edit to add 2 more headers as below.<br \/>\n<div class=\"su-note\"  style=\"border-color:#e5dd9d;border-radius:3px;-moz-border-radius:3px;-webkit-border-radius:3px;\"><div class=\"su-note-inner su-u-clearfix su-u-trim\" style=\"background-color:#FFF7B7;border-color:#ffffff;color:#333333;border-radius:3px;-moz-border-radius:3px;-webkit-border-radius:3px;\">If you not sure about SOAP Action then find out from SoapUI &gt; <strong>Raw Tab<\/strong> 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 &gt; Headers Grid &gt; Raw Edit.\u00a0 <\/div><\/div>\n<pre class=\"lang:xhtml decode:true\">Content-Type: text\/xml;charset=UTF-8\r\nSOAPAction: \"http:\/\/some-url-of-your-api-call\"<\/pre>\n<p>SOAPAction URL is also listed under the following place in SoapUI.<\/p>\n<div id=\"attachment_643\" style=\"width: 629px\" class=\"wp-caption alignnone\"><a href=\"\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/06\/find-soapaction-header-soap-request-call.png\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-643\" class=\"size-full wp-image-643\" src=\"\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/06\/find-soapaction-header-soap-request-call.png\" alt=\"How to find SOAPAction Header value using SoapUI tool\" width=\"619\" height=\"310\" srcset=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/06\/find-soapaction-header-soap-request-call.png 619w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/06\/find-soapaction-header-soap-request-call-300x150.png 300w\" sizes=\"(max-width: 619px) 100vw, 619px\" \/><\/a><p id=\"caption-attachment-643\" class=\"wp-caption-text\">How to find SOAPAction Header value using SoapUI tool<\/p><\/div><\/li>\n<\/ol>\n<\/li>\n<li>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).<\/li>\n<li>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\u00a0below screenshot and add variable anywhere in the text.<\/li>\n<li>You can also edit Filter to control how data is returned. If you have\u00a0Nested nodes in XML response you can select data node which you care about.\u00a0Click select Filter button to\u00a0navigate through hierarchy.\n<div id=\"attachment_649\" style=\"width: 632px\" class=\"wp-caption alignnone\"><a href=\"\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/06\/select-xml-response-filter.png\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-649\" class=\"size-full wp-image-649\" src=\"\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/06\/select-xml-response-filter.png\" alt=\"Filter SOAP Web Service - XML response data\" width=\"622\" height=\"477\" srcset=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/06\/select-xml-response-filter.png 622w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/06\/select-xml-response-filter-300x230.png 300w\" sizes=\"(max-width: 622px) 100vw, 622px\" \/><\/a><p id=\"caption-attachment-649\" class=\"wp-caption-text\">Filter SOAP Web Service &#8211; XML response data<\/p><\/div><\/li>\n<li>Un-check include parent option if you don&#8217;t care about getting parent attributes.<\/li>\n<li>Your final UI may look like below after setting Url, Method, Body and Headers.\n<div id=\"attachment_644\" style=\"width: 710px\" class=\"wp-caption alignnone\"><a href=\"\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/06\/calling-soap-web-service-api-in-ssis.png\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-644\" class=\"wp-image-644\" src=\"\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/06\/calling-soap-web-service-api-in-ssis.png\" alt=\"Calling SOAP Web Service in SSIS using XML Source Connector\" width=\"700\" height=\"495\" srcset=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/06\/calling-soap-web-service-api-in-ssis.png 903w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/06\/calling-soap-web-service-api-in-ssis-300x212.png 300w\" sizes=\"(max-width: 700px) 100vw, 700px\" \/><\/a><p id=\"caption-attachment-644\" class=\"wp-caption-text\">Calling SOAP Web Service in SSIS using XML Source Connector<\/p><\/div><\/li>\n<li>\u00a0On 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 <strong>a:row<\/strong> 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 ( <strong>$.a:response.a:data.a:row[*]<\/strong>\u00a0) .\n<pre class=\"lang:default highlight:0 decode:true\">&lt;a:response&gt;\r\n\t&lt;a:data&gt;\r\n\t\t&lt;a:row&gt;....&lt;\/a:row&gt;\r\n\t\t&lt;a:row&gt;....&lt;\/a:row&gt;\r\n\t&lt;\/a:data&gt;\r\n&lt;\/a:response&gt;<\/pre>\n<p>&nbsp;<\/li>\n<li>Now once all settings entered. Click Preview.\n<div id=\"attachment_646\" style=\"width: 742px\" class=\"wp-caption alignnone\"><a href=\"\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/06\/filter-preview-soap-web-service-response-in-table-format.png\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-646\" class=\"size-full wp-image-646\" src=\"\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/06\/filter-preview-soap-web-service-response-in-table-format.png\" alt=\"Set Filter and Preview SOAP Web Service Response in Table Format \" width=\"732\" height=\"293\" srcset=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/06\/filter-preview-soap-web-service-response-in-table-format.png 732w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/06\/filter-preview-soap-web-service-response-in-table-format-300x120.png 300w\" sizes=\"(max-width: 732px) 100vw, 732px\" \/><\/a><p id=\"caption-attachment-646\" class=\"wp-caption-text\">Set Filter and Preview SOAP Web Service Response in Table Format<\/p><\/div><\/li>\n<\/ol>\n<h2>Creating SOAP Request with Optional Parameters<\/h2>\n<p>Sometimes if you create SOAP request you may not see optional parameters. Here is how you can create SOAP Request with optional parameters.<\/p>\n<div id=\"attachment_2812\" style=\"width: 817px\" class=\"wp-caption alignnone\"><a href=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/06\/create-soap-request-with-optional-parameters-soapui.png\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-2812\" class=\"size-full wp-image-2812\" src=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/06\/create-soap-request-with-optional-parameters-soapui.png\" alt=\"Create SOAP Request XML (With Optional Parameters)\" width=\"807\" height=\"315\" srcset=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/06\/create-soap-request-with-optional-parameters-soapui.png 807w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/06\/create-soap-request-with-optional-parameters-soapui-300x117.png 300w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/06\/create-soap-request-with-optional-parameters-soapui-768x300.png 768w\" sizes=\"(max-width: 807px) 100vw, 807px\" \/><\/a><p id=\"caption-attachment-2812\" class=\"wp-caption-text\">Create SOAP Request XML (With Optional Parameters)<\/p><\/div>\n<h2>Load XML Data into SQL Server<\/h2>\n<p>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.<\/p>\n<div id=\"attachment_647\" style=\"width: 367px\" class=\"wp-caption alignnone\"><a href=\"\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/06\/ssis-soap-web-service-loading-xml-data-into-sql-server-table.png\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-647\" class=\"size-full wp-image-647\" src=\"\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/06\/ssis-soap-web-service-loading-xml-data-into-sql-server-table.png\" alt=\"SSIS SOAP Web Service Call - Loading XML data into SQL Server Table (or MySQL, Oracle, FlatFile)\" width=\"357\" height=\"278\" srcset=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/06\/ssis-soap-web-service-loading-xml-data-into-sql-server-table.png 357w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/06\/ssis-soap-web-service-loading-xml-data-into-sql-server-table-300x234.png 300w\" sizes=\"(max-width: 357px) 100vw, 357px\" \/><\/a><p id=\"caption-attachment-647\" class=\"wp-caption-text\">SSIS SOAP Web Service Call &#8211; Loading XML data into SQL Server Table (or MySQL, Oracle, FlatFile)<\/p><\/div>\n<h2>Sample SOAP Webservices for Testing (FREE Public API)<\/h2>\n<p>If you like to test few more scenarios and you do not have SOAP service credentials then no worries. <a href=\"https:\/\/documenter.getpostman.com\/view\/8854915\/Szf26WHn?version=latest\" target=\"_blank\" rel=\"noopener\">Try this link<\/a>. It lists many FREE SOAP services which can be used for testing purpose without real credentials.<\/p>\n<p><strong>Example Request:<\/strong><\/p>\n<p>Here is sample SOAP API call to convert number into word (i.e. input=500, output=five hundred).<br \/>\nEnter Method, URL, Headers and Body like below on ZappySys UI<\/p>\n<pre class=\"lang:default highlight:0 decode:true \">POST https:\/\/www.dataaccess.com\/webservicesserver\/NumberConversion.wso\r\nContent-Type: text\/xml; charset=utf-8\r\n\r\n&lt;?xml version=\"1.0\" encoding=\"utf-8\"?&gt;\r\n&lt;soap:Envelope xmlns:soap=\"http:\/\/schemas.xmlsoap.org\/soap\/envelope\/\"&gt;\r\n  &lt;soap:Body&gt;\r\n    &lt;NumberToWords xmlns=\"http:\/\/www.dataaccess.com\/webservicesserver\/\"&gt;\r\n      &lt;ubiNum&gt;500&lt;\/ubiNum&gt;\r\n    &lt;\/NumberToWords&gt;\r\n  &lt;\/soap:Body&gt;\r\n&lt;\/soap:Envelope&gt;<\/pre>\n<p><strong>Response<\/strong><\/p>\n<pre class=\"lang:default highlight:0 decode:true \">&lt;?xml version=\"1.0\" encoding=\"utf-8\"?&gt;\r\n&lt;soap:Envelope xmlns:soap=\"http:\/\/schemas.xmlsoap.org\/soap\/envelope\/\"&gt;\r\n  &lt;soap:Body&gt;\r\n    &lt;m:NumberToWordsResponse xmlns:m=\"http:\/\/www.dataaccess.com\/webservicesserver\/\"&gt;\r\n      &lt;m:NumberToWordsResult&gt;five hundred &lt;\/m:NumberToWordsResult&gt;\r\n    &lt;\/m:NumberToWordsResponse&gt;\r\n  &lt;\/soap:Body&gt;\r\n&lt;\/soap:Envelope&gt;<\/pre>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<h2>SOAP API Pagination (Loop through large dataset)<\/h2>\n<p>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.<\/p>\n<p><a href=\"\/\/zappysys.com\/blog\/get-data-from-google-adwords-using-ssis\/\">How to get data from Google AdWords using SSIS<\/a><\/p>\n<p><a href=\"\/\/zappysys.com\/blog\/ssis-rest-api-looping-until-no-more-pages-found\/\" target=\"_blank\" rel=\"noopener\">Various Paging methods for REST API<\/a><\/p>\n<h2>Sending complex SOAP Request (Nested Parent-Child Array)<\/h2>\n<p>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 <a href=\"https:\/\/zappysys.zendesk.com\/hc\/en-us\/articles\/360002575234-How-to-generate-Workday-SOAP-Request-XML-in-SSIS\" target=\"_blank\" rel=\"noopener\">this article<\/a>\u00a0for real-world example.<\/p>\n<div id=\"attachment_3167\" style=\"width: 674px\" class=\"wp-caption alignnone\"><a href=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/01\/ssis-create-soap-request-nested-xml-multiple-inputs-post-data.png\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-3167\" class=\"size-full wp-image-3167\" src=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/01\/ssis-create-soap-request-nested-xml-multiple-inputs-post-data.png\" alt=\"Create nested XML for SOAP Request - Multiple inputs (POST data to API using SSIS Web API Destination)\" width=\"664\" height=\"520\" srcset=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/01\/ssis-create-soap-request-nested-xml-multiple-inputs-post-data.png 664w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/01\/ssis-create-soap-request-nested-xml-multiple-inputs-post-data-300x235.png 300w\" sizes=\"(max-width: 664px) 100vw, 664px\" \/><\/a><p id=\"caption-attachment-3167\" class=\"wp-caption-text\">Create nested XML for SOAP Request &#8211; Multiple inputs (POST data to API using SSIS Web API Destination)<\/p><\/div>\n<h2>Extract single XML node from SOAP Response<\/h2>\n<p>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&#8217;s the case then <a href=\"https:\/\/zappysys.com\/blog\/ssis-extract-single-xml-node-using-xpath-soap-response\/\" target=\"_blank\" rel=\"noopener\">refer to this article<\/a>.<\/p>\n<h2>Conclusion<\/h2>\n<p>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. <a href=\"\/\/zappysys.com\/products\/ssis-powerpack\/\">SSIS PowerPack<\/a> comes with many RESTful API connectors including SOAP\/XML Source and JSON Source. Try <a href=\"\/\/zappysys.com\/products\/ssis-powerpack\/\">SSIS PowerPack<\/a> yourself and discover many features not discussed in this article.<\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Introduction In this article, you will learn how to call SOAP Web Service in SSIS.\u00a0We will use drag and\u00a0drop 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 [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":2543,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[62],"tags":[67,113,4,7,100],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v22.3 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\r\n<title>Calling SOAP Web Service in SSIS (XML Source) | ZappySys Blog<\/title>\r\n<meta name=\"description\" content=\"Learn how to call SOAP Web service using SSIS. Extract data using XML Source connector, Generate SOAP requests using SoapUI Testing tool.\" \/>\r\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\r\n<link rel=\"canonical\" href=\"https:\/\/zappysys.com\/blog\/calling-soap-web-service-in-ssis-xml-source\/\" \/>\r\n<meta property=\"og:locale\" content=\"en_US\" \/>\r\n<meta property=\"og:type\" content=\"article\" \/>\r\n<meta property=\"og:title\" content=\"Calling SOAP Web Service in SSIS (XML Source) | ZappySys Blog\" \/>\r\n<meta property=\"og:description\" content=\"Learn how to call SOAP Web service using SSIS. Extract data using XML Source connector, Generate SOAP requests using SoapUI Testing tool.\" \/>\r\n<meta property=\"og:url\" content=\"https:\/\/zappysys.com\/blog\/calling-soap-web-service-in-ssis-xml-source\/\" \/>\r\n<meta property=\"og:site_name\" content=\"ZappySys Blog\" \/>\r\n<meta property=\"article:author\" content=\"https:\/\/www.facebook.com\/ZappySys\/\" \/>\r\n<meta property=\"article:published_time\" content=\"2016-06-23T13:51:11+00:00\" \/>\r\n<meta property=\"article:modified_time\" content=\"2020-10-07T16:35:08+00:00\" \/>\r\n<meta property=\"og:image\" content=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/01\/create-workday-soap-request-using-soapui-api-xml-body.png\" \/>\r\n\t<meta property=\"og:image:width\" content=\"735\" \/>\r\n\t<meta property=\"og:image:height\" content=\"451\" \/>\r\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\r\n<meta name=\"author\" content=\"ZappySys\" \/>\r\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\r\n<meta name=\"twitter:creator\" content=\"@https:\/\/twitter.com\/zappysys\/\" \/>\r\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"ZappySys\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"10 minutes\" \/>\r\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/zappysys.com\/blog\/calling-soap-web-service-in-ssis-xml-source\/\",\"url\":\"https:\/\/zappysys.com\/blog\/calling-soap-web-service-in-ssis-xml-source\/\",\"name\":\"Calling SOAP Web Service in SSIS (XML Source) | ZappySys Blog\",\"isPartOf\":{\"@id\":\"https:\/\/zappysys.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/zappysys.com\/blog\/calling-soap-web-service-in-ssis-xml-source\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/zappysys.com\/blog\/calling-soap-web-service-in-ssis-xml-source\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/01\/create-workday-soap-request-using-soapui-api-xml-body.png\",\"datePublished\":\"2016-06-23T13:51:11+00:00\",\"dateModified\":\"2020-10-07T16:35:08+00:00\",\"author\":{\"@id\":\"https:\/\/zappysys.com\/blog\/#\/schema\/person\/2756c237457fbc95d82cb38962f81f82\"},\"description\":\"Learn how to call SOAP Web service using SSIS. Extract data using XML Source connector, Generate SOAP requests using SoapUI Testing tool.\",\"breadcrumb\":{\"@id\":\"https:\/\/zappysys.com\/blog\/calling-soap-web-service-in-ssis-xml-source\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/zappysys.com\/blog\/calling-soap-web-service-in-ssis-xml-source\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/zappysys.com\/blog\/calling-soap-web-service-in-ssis-xml-source\/#primaryimage\",\"url\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/01\/create-workday-soap-request-using-soapui-api-xml-body.png\",\"contentUrl\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/01\/create-workday-soap-request-using-soapui-api-xml-body.png\",\"width\":735,\"height\":451,\"caption\":\"Create SOAP Request Body from WSDL (Using SoapUI tool)\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/zappysys.com\/blog\/calling-soap-web-service-in-ssis-xml-source\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/zappysys.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Calling SOAP Web Service in SSIS (XML Source)\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/zappysys.com\/blog\/#website\",\"url\":\"https:\/\/zappysys.com\/blog\/\",\"name\":\"ZappySys Blog\",\"description\":\"SSIS \/ ODBC Drivers \/ API Connectors for JSON, XML, Azure, Amazon AWS, Salesforce, MongoDB and more\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/zappysys.com\/blog\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/zappysys.com\/blog\/#\/schema\/person\/2756c237457fbc95d82cb38962f81f82\",\"name\":\"ZappySys\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/zappysys.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/5c9be148088ba9b8af8e955c5f7c22b5?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/5c9be148088ba9b8af8e955c5f7c22b5?s=96&d=mm&r=g\",\"caption\":\"ZappySys\"},\"sameAs\":[\"http:\/\/www.zappysys.com\/\",\"https:\/\/www.facebook.com\/ZappySys\/\",\"https:\/\/twitter.com\/https:\/\/twitter.com\/zappysys\/\"],\"url\":\"https:\/\/zappysys.com\/blog\/author\/admin\/\"}]}<\/script>\r\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Calling SOAP Web Service in SSIS (XML Source) | ZappySys Blog","description":"Learn how to call SOAP Web service using SSIS. Extract data using XML Source connector, Generate SOAP requests using SoapUI Testing tool.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/zappysys.com\/blog\/calling-soap-web-service-in-ssis-xml-source\/","og_locale":"en_US","og_type":"article","og_title":"Calling SOAP Web Service in SSIS (XML Source) | ZappySys Blog","og_description":"Learn how to call SOAP Web service using SSIS. Extract data using XML Source connector, Generate SOAP requests using SoapUI Testing tool.","og_url":"https:\/\/zappysys.com\/blog\/calling-soap-web-service-in-ssis-xml-source\/","og_site_name":"ZappySys Blog","article_author":"https:\/\/www.facebook.com\/ZappySys\/","article_published_time":"2016-06-23T13:51:11+00:00","article_modified_time":"2020-10-07T16:35:08+00:00","og_image":[{"width":735,"height":451,"url":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/01\/create-workday-soap-request-using-soapui-api-xml-body.png","type":"image\/png"}],"author":"ZappySys","twitter_card":"summary_large_image","twitter_creator":"@https:\/\/twitter.com\/zappysys\/","twitter_misc":{"Written by":"ZappySys","Est. reading time":"10 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/zappysys.com\/blog\/calling-soap-web-service-in-ssis-xml-source\/","url":"https:\/\/zappysys.com\/blog\/calling-soap-web-service-in-ssis-xml-source\/","name":"Calling SOAP Web Service in SSIS (XML Source) | ZappySys Blog","isPartOf":{"@id":"https:\/\/zappysys.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/zappysys.com\/blog\/calling-soap-web-service-in-ssis-xml-source\/#primaryimage"},"image":{"@id":"https:\/\/zappysys.com\/blog\/calling-soap-web-service-in-ssis-xml-source\/#primaryimage"},"thumbnailUrl":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/01\/create-workday-soap-request-using-soapui-api-xml-body.png","datePublished":"2016-06-23T13:51:11+00:00","dateModified":"2020-10-07T16:35:08+00:00","author":{"@id":"https:\/\/zappysys.com\/blog\/#\/schema\/person\/2756c237457fbc95d82cb38962f81f82"},"description":"Learn how to call SOAP Web service using SSIS. Extract data using XML Source connector, Generate SOAP requests using SoapUI Testing tool.","breadcrumb":{"@id":"https:\/\/zappysys.com\/blog\/calling-soap-web-service-in-ssis-xml-source\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/zappysys.com\/blog\/calling-soap-web-service-in-ssis-xml-source\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/zappysys.com\/blog\/calling-soap-web-service-in-ssis-xml-source\/#primaryimage","url":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/01\/create-workday-soap-request-using-soapui-api-xml-body.png","contentUrl":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/01\/create-workday-soap-request-using-soapui-api-xml-body.png","width":735,"height":451,"caption":"Create SOAP Request Body from WSDL (Using SoapUI tool)"},{"@type":"BreadcrumbList","@id":"https:\/\/zappysys.com\/blog\/calling-soap-web-service-in-ssis-xml-source\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/zappysys.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Calling SOAP Web Service in SSIS (XML Source)"}]},{"@type":"WebSite","@id":"https:\/\/zappysys.com\/blog\/#website","url":"https:\/\/zappysys.com\/blog\/","name":"ZappySys Blog","description":"SSIS \/ ODBC Drivers \/ API Connectors for JSON, XML, Azure, Amazon AWS, Salesforce, MongoDB and more","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/zappysys.com\/blog\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/zappysys.com\/blog\/#\/schema\/person\/2756c237457fbc95d82cb38962f81f82","name":"ZappySys","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/zappysys.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/5c9be148088ba9b8af8e955c5f7c22b5?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/5c9be148088ba9b8af8e955c5f7c22b5?s=96&d=mm&r=g","caption":"ZappySys"},"sameAs":["http:\/\/www.zappysys.com\/","https:\/\/www.facebook.com\/ZappySys\/","https:\/\/twitter.com\/https:\/\/twitter.com\/zappysys\/"],"url":"https:\/\/zappysys.com\/blog\/author\/admin\/"}]}},"_links":{"self":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/620"}],"collection":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/comments?post=620"}],"version-history":[{"count":22,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/620\/revisions"}],"predecessor-version":[{"id":9151,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/620\/revisions\/9151"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/media\/2543"}],"wp:attachment":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/media?parent=620"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/categories?post=620"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/tags?post=620"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}