Cannot find path needed to import data with SSIS from an Excel File that is on SharePoint Online

Question

I am trying to connect to an Excel-file in SharePoint Online, with the use of VisualStudio 2019/SSIS. When I Google, I see this article mentioned a lot: https://stackoverflow.com/questions/18128632/export-sharepoint-excel-sheet-to-sql-using-ssis

But the image seems to be from SharePoint on premise, alt least I cannot get to the path the way described.

What steps do I need to take to get to the path?

And what do I need tot enter in SSIS ODATA (or maybe an other connector) to be able to read data from an Excel-file that is on SharePoint-Online.

Answer 1

Hi @Eric Klein Goldewijk，

I did several research that it seems we could not directly use the excel file on sharepoint as source.

The ways suggested is downloading the files first and then use it.

The OData source includes support for the following data sources:

• Microsoft Dynamics AX Online and Microsoft Dynamics CRM Online
• SharePoint lists. To see all the lists on a SharePoint server, use the following URL: https://<server>/_vti_bin/ListData.svc. For more information about SharePoint URL conventions, see SharePoint Foundation REST Interface.

OData Source

Regards，

Zoe Hui

---

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

Answer 2

Hi @Eric Klein Goldewijk，

As said here, you may create the HTTP Connection to SharePoint File. Go to Connection Managers Pane and then right click and then choose New Connection.

You can go to SharePoint and then go to File and Right Click, Choose Properties and then you will see Address (URL) that is the Server URL you need to use in Connection above. Also provide the user name and password which has permission on the SharePoint File. Test the connection and it should be successful.

Regards，

Zoe Hui

---

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

Answer 3

Hi Zoe,

I have copied the path in the properties in SharePoint:

https://xxxxx.sharepoint.com/sites/Projectxxx/Gedeelde%20documenten/General/Datasets%20achter%20pilot%20Lokatie.xlsx

(the language is Dutch)

I have tried to set up the HTTP-connection the way it is described, with several files on different locations in SharePoint. Files that I can open and edit based on my credentials.

But I get this message, every time:

TITLE: Microsoft Visual Studio

------------------------------

The remote server returned an error: (403) Forbidden.

------------------------------

BUTTONS:

OK

------------------------------

Could it be that I need special permissions or that a setting in our SharePoint prevents me from connecting to the files?

Regards,

Eric

Answer 4

Hi Zoe,

Thank you for your answer, it is clear to me that using Excel on SharePoint directly as a source is not possible.

Do you have any idea why I'm getting the "The remote server returned an error: (403) Forbidden" message?

Update:

I used the Script component mentioned in the article, I still get the 403 error.

In SSIS the error message is a bit more infomative:

Error: Server returned error code "403 - Forbidden". This error can occur when the specified resource needs "https" access, but the certificate validity period has expired, the certificate is not valid for the use requested, or the certificate has been revoked or revocation can not be checked. error mentioned.

Any thoughts on how to resolve this?

Regards,

Eric