Please visit this page to see all Pre-Configured ready to use API connectors which you can use in SSIS API Source / SSIS API Destination OR API ODBC Driver (for non-SSIS Apps such as Excel, Power BI, Informatica).
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.
- Firstly, you will need SSDT installed.
- Secondly, SSIS PowerPack installed.
- SoapUI installed.
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.
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
- Import WSDL file or URL for your Service in SoapUI for testing.
- File > New SOAP Project.
- In the Initial WSDL field enter
- Add credentials for SOAP Web service (Basic Auth or WSS)
- Create SOAP Request XML (Edit default parameters) and Click Play Button to test your request. Associative photo:
- If your test works in SoapUI then you are ready to move to SSIS part.
- 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.
In the next section, we will see how to call SharePoint SOAP Web Service in SSIS.
Now let’s move onto SSIS and get some data from SharePoint using XML Source.
- Open SSIS, create a new Integration project and create a new SSIS package.
- Drag and drop Data Flow onto the Control Flow from SSIS Toolbox.
- 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).
- Then configure XML Source in this way:
- Enter URL:
https://my.sharepoint.site.com/_vti_bin/Lists.asmx (depends if you have many sites or just one).
- Check Use Credentials checkbox and create ZS-HTTP connection. Use Basic or NTLM authorization scheme.
- Then copy configuration from SoapUI to XML Source. E.g. HTTP Request Method, Body, Body Content-Type, HTTP Headers (e.g. SOAPAction header).
- 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.
- Enter URL:
- What’s left is to connect XML Source with OLEDB Destination or a similar destination component:
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.