Connecting SSIS to SharePoint gives error 403

Tahir Mustafa 1 Reputation point
2022-09-20T08:16:27.497+00:00

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

242826-image.png

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

242865-image.png

But with other authentication type I got different errors:

242913-image.png

242877-image.png

242914-image.png

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?

SharePoint
SharePoint
A group of Microsoft Products and technologies used for sharing and managing content, knowledge, and applications.
9,592 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,451 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. ZoeHui-MSFT 32,506 Reputation points
    2022-09-20T08:34:55.287+00:00

    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.