How can I download an Excel file from a SharePoint document library that requires authentication?

JimmyG 25 Reputation points
2024-01-12T21:01:48.63+00:00

Using SSIS, I need to download an Excel file from my companies SharePoint site: https://jeffersonkyschools.sharepoint.com/sites/CTE From what I've read it seems like the best option to do this is using REST APIs, but what I'm having trouble with is how do I get authenticated to the site? I don't think creating an oData source connector is correct because that is for Lists, and I'm needing to download a document. I've tried creating an HTTP connector but it keeps giving me a 403: Forbidden error.
I feel like I'm trying to recreate the wheel here, but I can't believe I'm the first person that's ever wanted to just download a document file from a SharePoint site that requires authentication. Is there an easy solution to this to I'm just unaware of? Please say there is :)

SharePoint
SharePoint
A group of Microsoft Products and technologies used for sharing and managing content, knowledge, and applications.
11,209 questions
0 comments No comments
{count} votes

Accepted answer
  1. AllenXu-MSFT 23,666 Reputation points Microsoft Vendor
    2024-01-19T01:59:17.83+00:00

    Hi @JimmyG,

    I'm glad to hear you solve the problem, if you have any issue about SharePoint, you are welcome to raise a ticket in this forum.

    By the way, since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others.". and according to the scenario introduced here: Answering your own questions on Microsoft Q&A, I would make a brief summary of this thread:

    [How can I download an Excel file from a SharePoint document library that requires authentication?]

    Issue Symptom:
    Using SSIS, I need to download an Excel file from my companies SharePoint site: https://jeffersonkyschools.sharepoint.com/sites/CTE From what I've read it seems like the best option to do this is using REST APIs, but what I'm having trouble with is how do I get authenticated to the site? I don't think creating an OData source connector is correct because that is for Lists, and I'm needing to download a document. I've tried creating an HTTP connector, but it keeps giving me a 403: Forbidden error.

    Current status:
    We resolved the issue by downloading the OneDrive Sync App onto a SQL Server, syncing SharePoint to that Server, and then repointing the existing connections in the SSIS package to the new SQL Server location:

    1. Log onto the SQL Server using the service account that has access to the SQL job and SSIS package.
    2. Download the OneDrive Sync App and install on the SQL Server
    3. Log onto the SharePoint document library that has the files you need
    4. Click the Sync button button in the menu near the top of the page
      User's image
    5. Select "Open Microsoft Drive".
    6. At this point it will automatically create a new folder on the sql server drive to store the files in.
      User's image A second window may appear in SharePoint, if it does just select the Close button.
    7. Make this newly created folder shared and copy it's path.
    8. Modify the SSIS package to use the new path to the files.

    You could click the "Accept Answer" button for this summary to close this thread, and this can make it easier for other community members to see the useful information when reading this thread. Thanks for your understanding!

    1 person found this answer helpful.
    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. Wilkin Sanchez 186 Reputation points
    2024-01-14T01:22:51.5466667+00:00

    Hello @JimmyG,

    It's a bit complicated task, but you can try the following

    1. Register an App in Azure AD to get a client ID and secret.
    2. Assign the necessary Sharepoint permissions to that App you just created.
    3. Use the SharePoint Rest API with the access token to authenticate and download the file.

    a PowerShell script for that would look similar to this:

    # Variables
    $siteUrl = "https://jeffersonkyschools.sharepoint.com/sites/CTE" 
    $clientId = "<Your-Client-ID>" 
    $clientSecret = "<Your-Client-Secret>" 
    $localPath = "<Path-To-Save-The-File>" 
    $fileName = "file.xlsx" 
    $libraryFolderUrl = "Shared%20Documents/folder" 
    # Authenticate and Connect to SharePoint Online Connect-PnPOnline -Url 
    $siteUrl -ClientId $clientId -ClientSecret $clientSecret 
    # Download the file 
    Get-PnPFile -Url "/sites/CTE/$libraryFolderUrl/$fileName" -Path $localPath -AsFile 
    # Disconnect the session 
    Disconnect-PnPOnline
    

    On a side note, try not to show your company's real domain. It could be susceptible to spam.

    If the answer is helpful, please click "Accept Answer" or Yes and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    1 person found this answer helpful.

  2. JimmyG 25 Reputation points
    2024-01-18T13:21:18.3733333+00:00

    We resolved the issue by downloading the OneDrive Sync App onto a SQL Server, syncing SharePoint to that Server, and then repointing the existing connections in the SSIS package to the new SQL Server location:

    1. Log onto the SQL Server using the service account that has access to the SQL job and SSIS package.
    2. Download the OneDrive Sync App and install on the SQL Server
    3. Log onto the SharePoint document library that has the files you need
    4. Click the Sync button button in the menu near the top of the page
      User's image
    5. Select "Open Microsoft Drive".
      1. At this point it will automatically create a new folder on the sql server drive to store the files in.
        User's image
      A second window may appear in SharePoint, if it does just select the Close button.
    6. Make this newly created folder shared and copy it's path.
    7. Modify the SSIS package to use the new path to the files.
    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.