{"id":916,"date":"2015-11-28T17:06:02","date_gmt":"2015-11-28T22:06:02","guid":{"rendered":"http:\/\/binaryworld.net\/blogs\/?p=916"},"modified":"2015-12-01T16:37:05","modified_gmt":"2015-12-01T21:37:05","slug":"consume-xml-web-service-in-ssis-rest-api-or-soap","status":"publish","type":"post","link":"https:\/\/binaryworld.net\/blogs\/consume-xml-web-service-in-ssis-rest-api-or-soap\/","title":{"rendered":"Consume XML Web service in SSIS (REST API or SOAP)"},"content":{"rendered":"<div id=\"toc_container\" class=\"no_bullets\"><p class=\"toc_title\">Contents<\/p><ul class=\"toc_list\"><li><a href=\"#Introduction\"><span class=\"toc_number toc_depth_1\">1<\/span> Introduction<\/a><\/li><li><a href=\"#What_is_missing_in_Microsoft_SSIS_XML_Source\"><span class=\"toc_number toc_depth_1\">2<\/span> What is missing in Microsoft SSIS XML Source<\/a><\/li><li><a href=\"#ZappySys_SSIS_XML_Source_for_flexible_data_extract\"><span class=\"toc_number toc_depth_1\">3<\/span> ZappySys SSIS XML Source for flexible data extract<\/a><ul><li><a href=\"#Direct_URL_Read_from_XML_Web_service_URL_Call_XML_REST_API_Service_and_get_de-normalized_resultset\"><span class=\"toc_number toc_depth_2\">3.1<\/span> Direct URL \u2013 Read from XML Web service URL \u2013 Call XML REST API Service and get de-normalized resultset<\/a><\/li><li><a href=\"#Direct_File_Path_Read_data_from_XML_files_Single_or_Multiple_files_Use_wildcard_pattern_in_path\"><span class=\"toc_number toc_depth_2\">3.2<\/span> Direct File Path \u2013 Read data from XML files (Single or Multiple files) \u2013 Use wildcard pattern in path<\/a><\/li><li><a href=\"#Direct_Value_Read_from_XML_String\"><span class=\"toc_number toc_depth_2\">3.3<\/span> Direct Value \u2013 Read from XML String<\/a><\/li><\/ul><\/li><li><a href=\"#Conclusion\"><span class=\"toc_number toc_depth_1\">4<\/span> Conclusion<\/a><\/li><\/ul><\/div>\n<h2><span id=\"Introduction\">Introduction<\/span><\/h2>\n<p>Microsoft provides native XML Source adapter but most of users will find it totally unusable in many scenarios. In this post you will learn how to <em>read data from XML files<\/em> or <em>consume XML web service in SSIS using <\/em><a href=\"http:\/\/zappysys.com\/products\/ssis-powerpack\/ssis-xml-source\/\">ZappySys SSIS XML Source<\/a>. You will also learn how to overcome many shortcoming in <a href=\"https:\/\/msdn.microsoft.com\/en-us\/library\/ms140277.aspx\" target=\"_blank\">Microsoft SSIS XML Source<\/a> in few clicks.<\/p>\n<h2><span id=\"What_is_missing_in_Microsoft_SSIS_XML_Source\">What is missing in Microsoft SSIS XML Source<\/span><\/h2>\n<p>Sometimes you will face too much trouble when using out of the box Native SSIS XML Source. Few points highlighted below.<\/p>\n<ul>\n<li>Microsoft SSIS XML Source Can&#8217;t consume XML data from Web service (such as SOAP Web service or REST API Web service)<\/li>\n<li>Using Microsoft SSIS XML Source you Can&#8217;t read data from multiple XML files easily (e.g. c:\\cust*.xml)<\/li>\n<li>If you want to de-normalize nested datasets then you cant do either. If you have 10 nested levels then Microsoft SSIS XML Source will produce 10 Outputs and making it totally useless)<\/li>\n<li>Microsoft SSIS XML Source &#8211; Doesn&#8217;t support XPath like expressions<\/li>\n<li>No support for parent-child relationship linking in Microsoft SSIS XML Source<\/li>\n<\/ul>\n<h2><span id=\"ZappySys_SSIS_XML_Source_for_flexible_data_extract\">ZappySys SSIS XML Source for flexible data extract<\/span><\/h2>\n<p><a href=\"http:\/\/zappysys.com\/products\/ssis-powerpack\/ssis-xml-source\/\" target=\"_blank\">ZappySys SSIS XML Source<\/a> is one of many Adapters\/Tasks which comes with <a href=\"http:\/\/zappysys.com\/products\/ssis-powerpack\/\" target=\"_blank\">SSIS PowerPack<\/a>. ZappySys takes very unique and innovative approach compared to Microsoft native SSIS XML Source.<\/p>\n<p>Unlike Microsoft XML Source ZappySys XML Adapter doesn&#8217;t produce multiple outputs if you have nested sections but it produces Flat Dataset which is highly de-normalized. It includes parent columns and child columns in the same row e.g. Customer info + \u00a0order info if you have orders nested inside customer node.<\/p>\n<p>It allows you to control which section you want to extract using Filter expression (Think like XPath but not exactly same syntax. Its similar to JSONPath expression language). You can also consume local XML files or consume Remote Web service in XML format.<\/p>\n<h3><span id=\"Direct_URL_Read_from_XML_Web_service_URL_Call_XML_REST_API_Service_and_get_de-normalized_resultset\">Direct URL \u2013 Read from XML Web service URL \u2013 Call XML REST API Service and get de-normalized resultset<\/span><\/h3>\n<p><img decoding=\"async\" src=\"http:\/\/zappysys.com\/onlinehelp\/ssis-powerpack\/scr\/images\/xml-source\/ssis-xml-source-data-from-web-url.png\" alt=\"SSIS XML Source - Direct Mode - Call REST API web service or read from XML File\" \/><\/p>\n<h3><span id=\"Direct_File_Path_Read_data_from_XML_files_Single_or_Multiple_files_Use_wildcard_pattern_in_path\">Direct File Path \u2013 Read data from XML files (Single or Multiple files) \u2013 Use wildcard pattern in path<\/span><\/h3>\n<p><img decoding=\"async\" src=\"http:\/\/zappysys.com\/onlinehelp\/ssis-powerpack\/scr\/images\/xml-source\/ssis-xml-source-read-from-multiple-files.png\" alt=\"SSIS XML Source - Read data from XML files (Single or Multiple files) - Use wildcard pattern in path\" \/>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <img decoding=\"async\" src=\"http:\/\/zappysys.com\/onlinehelp\/ssis-powerpack\/scr\/images\/xml-source\/ssis-xml-source-read-multiple-files-wildcard-pattern.png\" alt=\"SSIS XML Source - Read data from XML files (Single or Multiple files) - Use wildcard pattern in path\" \/><\/p>\n<h3><span id=\"Direct_Value_Read_from_XML_String\">Direct Value \u2013 Read from XML String<\/span><\/h3>\n<p><img decoding=\"async\" src=\"http:\/\/zappysys.com\/onlinehelp\/ssis-powerpack\/scr\/images\/xml-source\/ssis-xml-source-data-read-from-direct-string.png\" alt=\"Read XML data in SSIS\" \/><\/p>\n<h2><span id=\"Conclusion\">Conclusion<\/span><\/h2>\n<p>Consuming XML Web service in SSIS can be tricky but using <a href=\"http:\/\/zappysys.com\/products\/ssis-powerpack\/ssis-xml-source\/\">ZappySys SSIS XML<\/a> Source you can surely cut down most of you headache. It supports\u00a0multiple modes of consuming local XML files or Web Service API. It&#8217;s innovative approach of de-normalization can give you big advantage compared to Microsoft Native SSIS XML Source.<\/p>\n<p>Download SSIS PowerPack<\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Contents1 Introduction2 What is missing in Microsoft SSIS XML Source3 ZappySys SSIS XML Source for flexible data extract3.1 Direct URL \u2013 Read from XML Web service URL \u2013 Call XML REST API Service and get de-normalized resultset3.2 Direct File Path &hellip;<\/p>\n<p class=\"read-more\"> <a class=\"more-link\" href=\"https:\/\/binaryworld.net\/blogs\/consume-xml-web-service-in-ssis-rest-api-or-soap\/\"> <span class=\"screen-reader-text\">Consume XML Web service in SSIS (REST API or SOAP)<\/span> Read More &raquo;<\/a><\/p>\n","protected":false},"author":1,"featured_media":917,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[29],"tags":[147,30,146,145],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v22.3 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\r\n<title>Consume XML Web service in SSIS (REST API or SOAP) - BinaryWorld Blog<\/title>\r\n<meta name=\"description\" content=\"In this post you will learn how to consume XML Web service using SSIS. You can use same technique to read data from XML files in SSIS or REST API Service.\" \/>\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:\/\/binaryworld.net\/blogs\/consume-xml-web-service-in-ssis-rest-api-or-soap\/\" \/>\r\n<meta property=\"og:locale\" content=\"en_US\" \/>\r\n<meta property=\"og:type\" content=\"article\" \/>\r\n<meta property=\"og:title\" content=\"Consume XML Web service in SSIS (REST API or SOAP) - BinaryWorld Blog\" \/>\r\n<meta property=\"og:description\" content=\"In this post you will learn how to consume XML Web service using SSIS. You can use same technique to read data from XML files in SSIS or REST API Service.\" \/>\r\n<meta property=\"og:url\" content=\"https:\/\/binaryworld.net\/blogs\/consume-xml-web-service-in-ssis-rest-api-or-soap\/\" \/>\r\n<meta property=\"og:site_name\" content=\"BinaryWorld Blog\" \/>\r\n<meta property=\"article:published_time\" content=\"2015-11-28T22:06:02+00:00\" \/>\r\n<meta property=\"article:modified_time\" content=\"2015-12-01T21:37:05+00:00\" \/>\r\n<meta property=\"og:image\" content=\"https:\/\/binaryworld.net\/blogs\/wp-content\/uploads\/2015\/11\/ssis-xml-source-data-from-web-url.png\" \/>\r\n\t<meta property=\"og:image:width\" content=\"704\" \/>\r\n\t<meta property=\"og:image:height\" content=\"595\" \/>\r\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\r\n<meta name=\"author\" content=\"Binary World\" \/>\r\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\r\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Binary World\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\r\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/binaryworld.net\/blogs\/consume-xml-web-service-in-ssis-rest-api-or-soap\/\",\"url\":\"https:\/\/binaryworld.net\/blogs\/consume-xml-web-service-in-ssis-rest-api-or-soap\/\",\"name\":\"Consume XML Web service in SSIS (REST API or SOAP) - BinaryWorld Blog\",\"isPartOf\":{\"@id\":\"https:\/\/binaryworld.net\/blogs\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/binaryworld.net\/blogs\/consume-xml-web-service-in-ssis-rest-api-or-soap\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/binaryworld.net\/blogs\/consume-xml-web-service-in-ssis-rest-api-or-soap\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/binaryworld.net\/blogs\/wp-content\/uploads\/2015\/11\/ssis-xml-source-data-from-web-url.png\",\"datePublished\":\"2015-11-28T22:06:02+00:00\",\"dateModified\":\"2015-12-01T21:37:05+00:00\",\"author\":{\"@id\":\"https:\/\/binaryworld.net\/blogs\/#\/schema\/person\/77cf0a9a512dd22bff93c6a1b6374fe0\"},\"description\":\"In this post you will learn how to consume XML Web service using SSIS. You can use same technique to read data from XML files in SSIS or REST API Service.\",\"breadcrumb\":{\"@id\":\"https:\/\/binaryworld.net\/blogs\/consume-xml-web-service-in-ssis-rest-api-or-soap\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/binaryworld.net\/blogs\/consume-xml-web-service-in-ssis-rest-api-or-soap\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/binaryworld.net\/blogs\/consume-xml-web-service-in-ssis-rest-api-or-soap\/#primaryimage\",\"url\":\"https:\/\/binaryworld.net\/blogs\/wp-content\/uploads\/2015\/11\/ssis-xml-source-data-from-web-url.png\",\"contentUrl\":\"https:\/\/binaryworld.net\/blogs\/wp-content\/uploads\/2015\/11\/ssis-xml-source-data-from-web-url.png\",\"width\":704,\"height\":595},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/binaryworld.net\/blogs\/consume-xml-web-service-in-ssis-rest-api-or-soap\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/binaryworld.net\/blogs\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Consume XML Web service in SSIS (REST API or SOAP)\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/binaryworld.net\/blogs\/#website\",\"url\":\"https:\/\/binaryworld.net\/blogs\/\",\"name\":\"BinaryWorld Blog\",\"description\":\"Tips and Tutorials for Microsoft SQL Server, SSIS, SSAS, Business Intelligence, C#, .net\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/binaryworld.net\/blogs\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/binaryworld.net\/blogs\/#\/schema\/person\/77cf0a9a512dd22bff93c6a1b6374fe0\",\"name\":\"Binary World\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/binaryworld.net\/blogs\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/eaea47799daa577835eb53e64dfd3e13?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/eaea47799daa577835eb53e64dfd3e13?s=96&d=mm&r=g\",\"caption\":\"Binary World\"},\"description\":\"Binary World is a Software Development company located in Atlanta, USA (since 2007). Binary World specialized in Business Intelligence, mobile, cloud computing and .Net Application Development.\",\"url\":\"https:\/\/binaryworld.net\/blogs\/author\/admin\/\"}]}<\/script>\r\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Consume XML Web service in SSIS (REST API or SOAP) - BinaryWorld Blog","description":"In this post you will learn how to consume XML Web service using SSIS. You can use same technique to read data from XML files in SSIS or REST API Service.","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:\/\/binaryworld.net\/blogs\/consume-xml-web-service-in-ssis-rest-api-or-soap\/","og_locale":"en_US","og_type":"article","og_title":"Consume XML Web service in SSIS (REST API or SOAP) - BinaryWorld Blog","og_description":"In this post you will learn how to consume XML Web service using SSIS. You can use same technique to read data from XML files in SSIS or REST API Service.","og_url":"https:\/\/binaryworld.net\/blogs\/consume-xml-web-service-in-ssis-rest-api-or-soap\/","og_site_name":"BinaryWorld Blog","article_published_time":"2015-11-28T22:06:02+00:00","article_modified_time":"2015-12-01T21:37:05+00:00","og_image":[{"width":704,"height":595,"url":"https:\/\/binaryworld.net\/blogs\/wp-content\/uploads\/2015\/11\/ssis-xml-source-data-from-web-url.png","type":"image\/png"}],"author":"Binary World","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Binary World","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/binaryworld.net\/blogs\/consume-xml-web-service-in-ssis-rest-api-or-soap\/","url":"https:\/\/binaryworld.net\/blogs\/consume-xml-web-service-in-ssis-rest-api-or-soap\/","name":"Consume XML Web service in SSIS (REST API or SOAP) - BinaryWorld Blog","isPartOf":{"@id":"https:\/\/binaryworld.net\/blogs\/#website"},"primaryImageOfPage":{"@id":"https:\/\/binaryworld.net\/blogs\/consume-xml-web-service-in-ssis-rest-api-or-soap\/#primaryimage"},"image":{"@id":"https:\/\/binaryworld.net\/blogs\/consume-xml-web-service-in-ssis-rest-api-or-soap\/#primaryimage"},"thumbnailUrl":"https:\/\/binaryworld.net\/blogs\/wp-content\/uploads\/2015\/11\/ssis-xml-source-data-from-web-url.png","datePublished":"2015-11-28T22:06:02+00:00","dateModified":"2015-12-01T21:37:05+00:00","author":{"@id":"https:\/\/binaryworld.net\/blogs\/#\/schema\/person\/77cf0a9a512dd22bff93c6a1b6374fe0"},"description":"In this post you will learn how to consume XML Web service using SSIS. You can use same technique to read data from XML files in SSIS or REST API Service.","breadcrumb":{"@id":"https:\/\/binaryworld.net\/blogs\/consume-xml-web-service-in-ssis-rest-api-or-soap\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/binaryworld.net\/blogs\/consume-xml-web-service-in-ssis-rest-api-or-soap\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/binaryworld.net\/blogs\/consume-xml-web-service-in-ssis-rest-api-or-soap\/#primaryimage","url":"https:\/\/binaryworld.net\/blogs\/wp-content\/uploads\/2015\/11\/ssis-xml-source-data-from-web-url.png","contentUrl":"https:\/\/binaryworld.net\/blogs\/wp-content\/uploads\/2015\/11\/ssis-xml-source-data-from-web-url.png","width":704,"height":595},{"@type":"BreadcrumbList","@id":"https:\/\/binaryworld.net\/blogs\/consume-xml-web-service-in-ssis-rest-api-or-soap\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/binaryworld.net\/blogs\/"},{"@type":"ListItem","position":2,"name":"Consume XML Web service in SSIS (REST API or SOAP)"}]},{"@type":"WebSite","@id":"https:\/\/binaryworld.net\/blogs\/#website","url":"https:\/\/binaryworld.net\/blogs\/","name":"BinaryWorld Blog","description":"Tips and Tutorials for Microsoft SQL Server, SSIS, SSAS, Business Intelligence, C#, .net","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/binaryworld.net\/blogs\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/binaryworld.net\/blogs\/#\/schema\/person\/77cf0a9a512dd22bff93c6a1b6374fe0","name":"Binary World","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/binaryworld.net\/blogs\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/eaea47799daa577835eb53e64dfd3e13?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/eaea47799daa577835eb53e64dfd3e13?s=96&d=mm&r=g","caption":"Binary World"},"description":"Binary World is a Software Development company located in Atlanta, USA (since 2007). Binary World specialized in Business Intelligence, mobile, cloud computing and .Net Application Development.","url":"https:\/\/binaryworld.net\/blogs\/author\/admin\/"}]}},"_links":{"self":[{"href":"https:\/\/binaryworld.net\/blogs\/wp-json\/wp\/v2\/posts\/916"}],"collection":[{"href":"https:\/\/binaryworld.net\/blogs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/binaryworld.net\/blogs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/binaryworld.net\/blogs\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/binaryworld.net\/blogs\/wp-json\/wp\/v2\/comments?post=916"}],"version-history":[{"count":0,"href":"https:\/\/binaryworld.net\/blogs\/wp-json\/wp\/v2\/posts\/916\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/binaryworld.net\/blogs\/wp-json\/wp\/v2\/media\/917"}],"wp:attachment":[{"href":"https:\/\/binaryworld.net\/blogs\/wp-json\/wp\/v2\/media?parent=916"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/binaryworld.net\/blogs\/wp-json\/wp\/v2\/categories?post=916"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/binaryworld.net\/blogs\/wp-json\/wp\/v2\/tags?post=916"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}