question

tommycrni-4715 avatar image
0 Votes"
tommycrni-4715 asked ZoeHui-MSFT edited

SSIS/ADF - read excel file from Sharepoint

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

azure-data-factoryoffice-teams-windows-itproazure-sql-databasesql-server-integration-services
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @tommycrni-4715 , according to your description, we removed the tag of'office-sharepoint-server-administration'. Thanks for your support and understanding. :)

0 Votes 0 ·

Hi @tommycrni-4715 ,

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

0 Votes 0 ·

1 Answer

ZoeHui-MSFT avatar image
0 Votes"
ZoeHui-MSFT answered ZoeHui-MSFT edited

Hi @tommycrni-4715,

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


· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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

0 Votes 0 ·

Hi @tommycrni-4715, 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.


0 Votes 0 ·