{"id":9545,"date":"2022-02-18T13:26:38","date_gmt":"2022-02-18T13:26:38","guid":{"rendered":"https:\/\/zappysys.com\/blog\/?p=9545"},"modified":"2025-03-19T14:03:33","modified_gmt":"2025-03-19T14:03:33","slug":"ssis-read-data-sharepoint-premises-soap-api","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/ssis-read-data-sharepoint-premises-soap-api\/","title":{"rendered":"Read data from SharePoint List in SSIS (On-Premises SOAP API)"},"content":{"rendered":"

Introduction<\/h2>\n
UPDATE:<\/strong>\u00a0ZappySys has released a brand new API Connector for SharePoint Online<\/a> which makes it much simpler to\u00a0Read\/Write SharePoint Data in SSIS<\/strong> compared to the steps listed in this article. You can still use steps from this article but if you are new to API or want to avoid learning curve with API then use newer approach.<\/p>\n

Please visit this page to see all<\/a>\u00a0Pre-Configured ready to use API connectors which you can use in SSIS API Source<\/a> \/ SSIS API Destination<\/a> OR\u00a0API ODBC Driver<\/a> (for non-SSIS Apps such as Excel, Power BI, Informatica).<\/p>\n<\/div><\/div>\nIn this article, we learn how to call SharePoint (on-premises) SOAP API Service in SSIS. If you intend to call SharePoint Online (part of Office 365), we suggest reading SSIS SharePoint Online Connector<\/a> article.<\/p>\n

\"icon<\/a>Using SOAP API with SharePoint in SSIS is a common requirement to Administer SharePoint. SharePoint on-premises is a nice Microsoft application used to share documents and collaborate with the company. You can have schedules, projects, documents, and more shared using a Web platform.<\/p>\n

In addition, it is a very useful tool that your company may need. It is also possible to automate, administer tasks using SOAP API.
\nIn this article, we will learn how to create SOAP API queries to SharePoint on-premises.<\/p>\n

Also, we will show our ZappySys SSIS PowerPack that includes very powerful tools to export SharePoint data from REST API to any other source.
\n

\n\n\n\n\n

\n\"REST<\/a><\/td>\n		SSIS REST API Web Service Task
\n<\/a><\/td>\n<\/tr>\n

\n<\/a>\"SSIS<\/a><\/td>\n		SSIS XML Source (File, REST, SOAP Web Service)<\/a><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

Requirements<\/h2>\n
    \n
  1. Firstly, you will need SSDT installed.<\/li>\n
  2. Secondly, SSIS PowerPack<\/a> installed.<\/li>\n
  3. SoapUI installed<\/a>.<\/li>\n<\/ol>\n

    Getting started<\/h2>\n

    We will start with SoapUI application. It will help us to get all the possible methods SharePoint SOAP API allows. Also, it will show the inner workings of how the requests should look, which we will later replicate on XML Source. SoapUI will be our polestar.<\/p>\n

    How to call SharePoint SOAP API (On-Premises)<\/h2>\n

    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

      \n
    1. Import WSDL file or URL for your Service in SoapUI for testing.\n
        \n
      1. File > New SOAP Project.<\/li>\n
      2. In the\u00a0Initial WSDL<\/em> field enter
        \nhttp:\/\/your.domain.com\/_vti_bin\/Lists.asmx?wsdl
        \nor
        \nhttp:\/\/your.domain.com\/my-site\/_vti_bin\/Lists.asmx?wsdl<\/em><\/li>\n<\/ol>\n<\/li>\n
      3. Add credentials for SOAP Web service (Basic Auth or WSS)<\/li>\n
      4. Create SOAP Request XML (Edit default parameters) and Click Play Button <\/strong>to test your request. Associative photo:\n
      5. If your test works in SoapUI then you are ready to move to SSIS part.<\/li>\n
      6. Copy Service URL, Request Body and Headers to Notepad — we will use it in SSIS.
        \nFor Header see Raw<\/strong>\u00a0Tab on SoapUI > Copy two lines\u00a0Content-Type<\/strong>\u00a0and\u00a0SOAPAction.<\/strong><\/p>\n
        \"Get<\/a>

        Get ContentType and SOAPAction Headers from SoapUI Raw tab<\/p><\/div><\/li>\n<\/ol>\n

        In the next section, we will see how to call SharePoint SOAP Web Service in SSIS.<\/p>\n

        How to read data from a SharePoint List via SOAP Web Service<\/h2>\n

        Now let’s move onto SSIS and get some data from SharePoint using XML Source<\/em>.<\/p>\n

          \n
        1. Open SSIS, create a new Integration project and create a new SSIS package.<\/li>\n
        2. Drag and drop Data Flow onto the Control Flow from SSIS Toolbox.<\/li>\n
        3. Then drag and drop\u00a0XML Source<\/a>\u00a0onto Data Flow (you may also use other similar connectors such as\u00a0REST API Task<\/a> or Web API Destination<\/a>).<\/li>\n
        4. Then configure XML Source<\/em> in this way:
          \n\"\"<\/p>\n
            \n
          1. Enter URL:
            \nhttps:\/\/my.sharepoint.site.com\/sites\/mysite\/_vti_bin\/Lists.asmx<\/span>
            \nor
            \nhttps:\/\/my.sharepoint.site.com\/_vti_bin\/Lists.asmx<\/span> (depends if you have many sites or just one).<\/li>\n
          2. Check Use Credentials<\/strong> checkbox and create ZS-HTTP<\/strong> connection. Use Basic<\/strong> or NTLM<\/strong> authorization scheme.<\/li>\n
          3. Then copy configuration from SoapUI to XML Source. E.g. HTTP Request Method<\/strong>, Body<\/strong>, Body Content-Type, HTTP Headers <\/strong>(e.g. SOAPAction header).<\/li>\n
          4. Use $.soap:Envelope.soap:Body.GetListItemsResponse.GetListItemsResult.listitems.rs:data.z:row[*]<\/strong> as filter. If it doesn’t work, use Select Filter<\/strong> button to select the path manually and select row<\/strong> node.<\/li>\n<\/ol>\n<\/li>\n
          5. What’s left is to connect XML Source with OLEDB Destination or a similar destination component:
            \n\"\"<\/li>\n<\/ol>\n

            Conclusion<\/h2>\n

            To conclude, we can say that using SSIS PowerPack component XML Source and SoapUI we could achieve our goal of getting data from SharePoint On-Premises. The next step could be writing data to SharePoint via the same SOAP API interface, but this time using\u00a0Web API Destination<\/a>\u00a0component.<\/p>\n","protected":false},"excerpt":{"rendered":"

            Introduction In this article, we learn how to call SharePoint (on-premises) SOAP API Service in SSIS. If you intend to call SharePoint Online (part of Office 365), we suggest reading SSIS SharePoint Online Connector article. Using SOAP API with SharePoint in SSIS is a common requirement to Administer SharePoint. SharePoint on-premises is a nice Microsoft […]<\/p>\n","protected":false},"author":3,"featured_media":3731,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[62],"tags":[272,319,67,5,7],"yoast_head":"\r\nRead data from SharePoint List in SSIS (On-Premises SOAP API) | ZappySys Blog<\/title>\r\n<meta name=\"description\" content=\"Read data from SharePoint List in SSIS (On-Premises SOAP API) - ZappySys Blog\" \/>\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\/ssis-read-data-sharepoint-premises-soap-api\/\" \/>\r\n<meta property=\"og:locale\" content=\"en_US\" \/>\r\n<meta property=\"og:type\" content=\"article\" \/>\r\n<meta property=\"og:title\" content=\"Read data from SharePoint List in SSIS (On-Premises SOAP API) | ZappySys Blog\" \/>\r\n<meta property=\"og:description\" content=\"Read data from SharePoint List in SSIS (On-Premises SOAP API) - ZappySys Blog\" \/>\r\n<meta property=\"og:url\" content=\"https:\/\/zappysys.com\/blog\/ssis-read-data-sharepoint-premises-soap-api\/\" \/>\r\n<meta property=\"og:site_name\" content=\"ZappySys Blog\" \/>\r\n<meta property=\"article:published_time\" content=\"2022-02-18T13:26:38+00:00\" \/>\r\n<meta property=\"article:modified_time\" content=\"2025-03-19T14:03:33+00:00\" \/>\r\n<meta property=\"og:image\" content=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/05\/sharepoint-icon.jpg\" \/>\r\n\t<meta property=\"og:image:width\" content=\"225\" \/>\r\n\t<meta property=\"og:image:height\" content=\"225\" \/>\r\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\r\n<meta name=\"author\" content=\"ZappySys\" \/>\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=\"ZappySys\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"5 minutes\" \/>\r\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/zappysys.com\/blog\/ssis-read-data-sharepoint-premises-soap-api\/\",\"url\":\"https:\/\/zappysys.com\/blog\/ssis-read-data-sharepoint-premises-soap-api\/\",\"name\":\"Read data from SharePoint List in SSIS (On-Premises SOAP API) | ZappySys Blog\",\"isPartOf\":{\"@id\":\"https:\/\/zappysys.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/zappysys.com\/blog\/ssis-read-data-sharepoint-premises-soap-api\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/zappysys.com\/blog\/ssis-read-data-sharepoint-premises-soap-api\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/05\/sharepoint-icon.jpg\",\"datePublished\":\"2022-02-18T13:26:38+00:00\",\"dateModified\":\"2025-03-19T14:03:33+00:00\",\"author\":{\"@id\":\"https:\/\/zappysys.com\/blog\/#\/schema\/person\/c146068d4cb053724ae5ce85759c3ada\"},\"description\":\"Read data from SharePoint List in SSIS (On-Premises SOAP API) - ZappySys Blog\",\"breadcrumb\":{\"@id\":\"https:\/\/zappysys.com\/blog\/ssis-read-data-sharepoint-premises-soap-api\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/zappysys.com\/blog\/ssis-read-data-sharepoint-premises-soap-api\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/zappysys.com\/blog\/ssis-read-data-sharepoint-premises-soap-api\/#primaryimage\",\"url\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/05\/sharepoint-icon.jpg\",\"contentUrl\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/05\/sharepoint-icon.jpg\",\"width\":225,\"height\":225,\"caption\":\"sharepoint icon\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/zappysys.com\/blog\/ssis-read-data-sharepoint-premises-soap-api\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/zappysys.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Read data from SharePoint List in SSIS (On-Premises SOAP API)\"}]},{\"@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\/c146068d4cb053724ae5ce85759c3ada\",\"name\":\"ZappySys\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/zappysys.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/60165d46d0b33aefb91df61a22b0e1f4?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/60165d46d0b33aefb91df61a22b0e1f4?s=96&d=mm&r=g\",\"caption\":\"ZappySys\"},\"url\":\"https:\/\/zappysys.com\/blog\/author\/gliaskus\/\"}]}<\/script>\r\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Read data from SharePoint List in SSIS (On-Premises SOAP API) | ZappySys Blog","description":"Read data from SharePoint List in SSIS (On-Premises SOAP API) - ZappySys Blog","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\/ssis-read-data-sharepoint-premises-soap-api\/","og_locale":"en_US","og_type":"article","og_title":"Read data from SharePoint List in SSIS (On-Premises SOAP API) | ZappySys Blog","og_description":"Read data from SharePoint List in SSIS (On-Premises SOAP API) - ZappySys Blog","og_url":"https:\/\/zappysys.com\/blog\/ssis-read-data-sharepoint-premises-soap-api\/","og_site_name":"ZappySys Blog","article_published_time":"2022-02-18T13:26:38+00:00","article_modified_time":"2025-03-19T14:03:33+00:00","og_image":[{"width":225,"height":225,"url":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/05\/sharepoint-icon.jpg","type":"image\/jpeg"}],"author":"ZappySys","twitter_card":"summary_large_image","twitter_misc":{"Written by":"ZappySys","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/zappysys.com\/blog\/ssis-read-data-sharepoint-premises-soap-api\/","url":"https:\/\/zappysys.com\/blog\/ssis-read-data-sharepoint-premises-soap-api\/","name":"Read data from SharePoint List in SSIS (On-Premises SOAP API) | ZappySys Blog","isPartOf":{"@id":"https:\/\/zappysys.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/zappysys.com\/blog\/ssis-read-data-sharepoint-premises-soap-api\/#primaryimage"},"image":{"@id":"https:\/\/zappysys.com\/blog\/ssis-read-data-sharepoint-premises-soap-api\/#primaryimage"},"thumbnailUrl":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/05\/sharepoint-icon.jpg","datePublished":"2022-02-18T13:26:38+00:00","dateModified":"2025-03-19T14:03:33+00:00","author":{"@id":"https:\/\/zappysys.com\/blog\/#\/schema\/person\/c146068d4cb053724ae5ce85759c3ada"},"description":"Read data from SharePoint List in SSIS (On-Premises SOAP API) - ZappySys Blog","breadcrumb":{"@id":"https:\/\/zappysys.com\/blog\/ssis-read-data-sharepoint-premises-soap-api\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/zappysys.com\/blog\/ssis-read-data-sharepoint-premises-soap-api\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/zappysys.com\/blog\/ssis-read-data-sharepoint-premises-soap-api\/#primaryimage","url":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/05\/sharepoint-icon.jpg","contentUrl":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/05\/sharepoint-icon.jpg","width":225,"height":225,"caption":"sharepoint icon"},{"@type":"BreadcrumbList","@id":"https:\/\/zappysys.com\/blog\/ssis-read-data-sharepoint-premises-soap-api\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/zappysys.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Read data from SharePoint List in SSIS (On-Premises SOAP API)"}]},{"@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\/c146068d4cb053724ae5ce85759c3ada","name":"ZappySys","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/zappysys.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/60165d46d0b33aefb91df61a22b0e1f4?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/60165d46d0b33aefb91df61a22b0e1f4?s=96&d=mm&r=g","caption":"ZappySys"},"url":"https:\/\/zappysys.com\/blog\/author\/gliaskus\/"}]}},"_links":{"self":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/9545"}],"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\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/comments?post=9545"}],"version-history":[{"count":13,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/9545\/revisions"}],"predecessor-version":[{"id":11270,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/9545\/revisions\/11270"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/media\/3731"}],"wp:attachment":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/media?parent=9545"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/categories?post=9545"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/tags?post=9545"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}