Connecting SSIS to SharePoint gives error 403

Question

Hi, I want to connect SSIS packages to my SharePoint, to get excel or csv file and insert it into SQL server 2019. I've tried HTTP and OData connections but both of them gives same error 403.

In Server URL I am typing URL in this format :

https://companydoamin.sharepoint.com/sites/site_name/Shared%20Documents/Forms/AllItems.aspx

I've also tried with http://mySharePointServer/mySite/_vti_bin/listdata.svc

With OData connection and authentication type as Basic Authentication, I've got the same error.

But with other authentication type I got different errors:

In last is it even possible to read data from excel or csv from SharePoint and insert it into SQL server and is the error from SharePoint Side or from SSIS side?

Answer 1

Hi @Tahir Mustafa ,

Ye can use the SharePoint URL to derive a REST API web address that will be suffixed with listdata.svc. It is important that we get a *listdata.svc URL as we will need it for establishing connection to the SharePoint list using the REST API in an SSIS package. What worked for me in terms of deriving the *listdata.svc URL was appending /_vti_bin/listdata.svc just after the site name in the above SharePoint list URL such that it becomes the following:

https://mydomain.sharepoint.com/sites/SPSDemo/_vti_bin/listdata.svc/  

Here is a tutorial about connecting to SharePoint, you may take a reference to see if you have miss any steps.

https://www.sqlshack.com/how-to-configure-odata-ssis-connection-for-sharepoint-online/

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.