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:
- Log onto the SQL Server using the service account that has access to the SQL job and SSIS package.
- Download the OneDrive Sync App and install on the SQL Server
- Log onto the SharePoint document library that has the files you need
- Click the Sync button button in the menu near the top of the page
- Select "Open Microsoft Drive".
- At this point it will automatically create a new folder on the sql server drive to store the files in.
A second window may appear in SharePoint, if it does just select the Close button.
- Make this newly created folder shared and copy it's path.
- 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!