Read data from SharePoint List in SSIS (On-Premises SOAP API)

Introduction

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.

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

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

	SSIS REST API Web Service Task
	SSIS XML Source (File, REST, SOAP Web Service)

Requirements

1. Firstly, you will need SSDT installed.
2. Secondly, SSIS PowerPack installed.
3. SoapUI installed.

Getting started

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.

How to call SharePoint SOAP API (On-Premises)

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. Import WSDL file or URL for your Service in SoapUI for testing.
   1. File > New SOAP Project.
   2. In the Initial WSDL field enter
      http://your.domain.com/_vti_bin/Lists.asmx?wsdl
      or
      http://your.domain.com/my-site/_vti_bin/Lists.asmx?wsdl
2. Add credentials for SOAP Web service (Basic Auth or WSS)
3. Create SOAP Request XML (Edit default parameters) and Click Play Button to test your request. Associative photo:
   

   Create SOAP Request Body from WSDL (Using SoapUI tool)

4. If your test works in SoapUI then you are ready to move to SSIS part.
5. Copy Service URL, Request Body and Headers to Notepad — we will use it in SSIS.
   For Header see Raw Tab on SoapUI > Copy two lines Content-Type and SOAPAction.
   

   Get ContentType and SOAPAction Headers from SoapUI Raw tab

In the next section, we will see how to call SharePoint SOAP Web Service in SSIS.

How to read data from a SharePoint List via SOAP Web Service

Now let’s move onto SSIS and get some data from SharePoint using XML Source.

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

Conclusion

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 Web API Destination component.