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

Eric Klein Goldewijk 20 Reputation points
2023-03-06T15:33:11.92+00:00

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.

Excel
Excel
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
1,440 questions
SharePoint
SharePoint
A group of Microsoft Products and technologies used for sharing and managing content, knowledge, and applications.
9,560 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,450 questions
0 comments No comments
{count} votes

Accepted answer
  1. ZoeHui-MSFT 32,426 Reputation points
    2023-03-09T05:45:18.65+00:00

    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.

    0 comments No comments

3 additional answers

Sort by: Most helpful
  1. ZoeHui-MSFT 32,426 Reputation points
    2023-03-07T06:29:11.9666667+00:00

    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.

    0 comments No comments

  2. Eric Klein Goldewijk 20 Reputation points
    2023-03-08T10:31:43.9433333+00:00

    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


  3. Eric Klein Goldewijk 20 Reputation points
    2023-03-10T09:35:41.7933333+00:00

    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

    0 comments No comments