SSIS/ADF - read excel file from Sharepoint

Question

SSIS/ADF - read excel file from Sharepoint

tommy crni 1

Hi,

I would like to know is it possible to read excel file stored on Sharepoint in SSIS or ADF? Our sharepoint is synced with my local machine and I use Excel connector is SSIS and that works if I run package locally ( because file location is C:\User...\file.xlsx).
But when we try to run it through SQL Agent job it fails due to credentials ( we need to have this option to have SQL job scheduler).

Is there any way or workaround how to read excel file from Sharepoint that works with SQL agent job ( or Integration Runtime). Or maybe to store excel file somewhere else ( Azure Data Lake). Please consider our excel files are strongly confidential.

Thank you,
BR Tom

Elsie Lu_MSFT 9,806 Reputation points

2021-10-22T07:58:04.41+00:00

Hi @tommy crni , according to your description, we removed the tag of'office-sharepoint-server-administration'. Thanks for your support and understanding. :)
ShaikMaheer-MSFT 38,631 Reputation points Microsoft Employee Moderator

2021-10-26T07:38:47.557+00:00

Hi @tommy crni ,

Just checking in to see if the below answer helped. If this answers your query, do click and upvote for the same. And, if you have any further query do let us know.

1 answer

Your answer

Elsie Lu_MSFT 9,806 Reputation points

2021-10-22T07:58:04.41+00:00

Hi @tommy crni , according to your description, we removed the tag of'office-sharepoint-server-administration'. Thanks for your support and understanding. :)
ShaikMaheer-MSFT 38,631 Reputation points Microsoft Employee Moderator

2021-10-26T07:38:47.557+00:00

Hi @tommy crni ,

Just checking in to see if the below answer helped. If this answers your query, do click and upvote for the same. And, if you have any further query do let us know.

Answer 1

ZoeHui-MSFT 41,541

Hi @tommy crni ,

May I know if you create a SQL Server Agent Proxy when run the SQL Agent job?

Please refer to create-a-sql-server-agent-proxy.

Hope the following links will be helpful:

how-to-configure-odata-ssis-connection-for-sharepoint-online

ssis-sharepoint-excel-as-datasource

You can copy data from Azure Files to any supported sink data store, or copy data from any supported source data store to Azure Files.

See Copy data from or to Azure Files by using Azure Data Factory.

Regards，

Zoe

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

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October

tommy crni 1 Reputation point

2021-10-22T10:12:07.62+00:00

Hi, Thank you,

What is our excel file is stored in Microsoft Teams and if we have Azure SQL Server where packages are running through ADF Integration runtime?

One of the link that you provided describes to download excel file from sharepoint locally, that can't work if we need to have SQL job ( because file location is still C:\Users...xlsx).

Regards, Tom
ZoeHui-MSFT 41,541 Reputation points

2021-10-26T08:03:32.477+00:00

Hi @tommy crni , check if OData source is helpful or not here.

In addition, use third-party tools is also a workaround, you may take a carefully reference online.

About Azure SQL Server where packages are running through ADF, I'd like suggest that you may open a new detailed case with Azure tag for more professional advice.

Share via

SSIS/ADF - read excel file from Sharepoint

1 answer

Your answer