SSIS/ADF - read excel file from Sharepoint

tommy crni 1 Reputation point
2021-10-21T21:06:09.607+00:00

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 SQL Database
Microsoft Teams
Microsoft Teams
A Microsoft customizable chat-based workspace.
9,031 questions
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,477 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

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. ZoeHui-MSFT 32,426 Reputation points
    2021-10-22T08:07:16.297+00:00

    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