copy files from sharepoint to ADF

You have 2 alternatives to achieve what you want to achieve :

Option 1: Using Power Automate to trigger Azure Data Factory

1. Create Power Automate Flow:
   • Use Power Automate to monitor a SharePoint document library.
   • Add a trigger such as "When a file is created or modified (properties only)" to watch for changes in SharePoint.
   • Add a step for file approval (e.g., using "Start and wait for an approval" action). The flow will wait until the file is approved.
2. Trigger ADF Pipeline from Power Automate:
   • After the file is approved, you can add a step to call an Azure Data Factory pipeline.
   • Use the "HTTP" action in Power Automate to invoke the ADF REST API to trigger the pipeline that pulls data from SharePoint.
   • Security: You can use a managed identity (from the ADF pipeline) to authenticate and access Azure resources securely.

Option 2: Using Azure Data Factory to Directly Access SharePoint Files

1. Create an ADF Pipeline:
   • In ADF, use the Copy Activity to copy files from SharePoint Online to Azure Storage.
   • For this, you need to set up a Linked Service in ADF for SharePoint Online. This connection can be authenticated using OAuth2, preferably via a service principal or a managed identity.
2. Set up Authentication with Managed Identity:
   • If you are using a managed identity, ensure that the ADF’s managed identity has been granted the necessary permissions in SharePoint and Azure.
   • On the SharePoint side, configure app registration with API permissions (for Graph API or SharePoint API), and assign the ADF’s managed identity the required role to access the files.
3. Ensure Secure Transfer to Azure Storage:
   • Once you’ve connected to SharePoint and can read files, the next step in the ADF pipeline is to write them to your Azure Storage Account.
   • Use a Managed Identity for the Azure Storage connection as well, ensuring no credentials are hardcoded.
4. Schedule the ADF Pipeline:
   • The pipeline can be scheduled or triggered automatically via Power Automate or Logic Apps once the file is approved in the SharePoint workflow.

Hi @Sourav ,

Thankyou for using MS Q&A platform .

To copy Excel files from SharePoint Online to Azure Storage using Power Automate and Azure Data Factory (ADF) with the best security approach, you can follow the steps below:

1. Create a Power Automate flow that triggers when a file is approved in SharePoint Online. You can use the "When a file is created or modified (properties only)" trigger to monitor the SharePoint Online library for file approval.
2. In the Power Automate flow, use the "Get file content" action to retrieve the contents of the approved Excel file from SharePoint Online.
3. Use the "Create blob" action in the Azure Blob Storage connector to create a new blob in Azure Storage and upload the contents of the Excel file to the blob.
4. To ensure the best security approach, you can use a managed identity to authenticate the Power Automate flow and ADF to access the SharePoint Online and Azure Storage resources. You can create a managed identity in Azure Active Directory and grant it the necessary permissions to access the SharePoint Online and Azure Storage resources.
5. In ADF, create a pipeline that triggers when the Power Automate flow completes the file approval action. You can use the "Web" activity in ADF to call the Power Automate flow and check the status of the file approval action.
6. If the file approval action is successful, use the "Copy data" activity in ADF to copy the Excel file from SharePoint Online to Azure Storage. You can use the SharePoint Online and Azure Blob Storage connectors in ADF to access the SharePoint Online and Azure Storage resources.
7. Use the managed identity to authenticate the ADF pipeline and grant it the necessary permissions to access the SharePoint Online and Azure Storage resources.

Kindly go through the below video for more details: SharePoint File Copy to Azure using Power Automate or Azure Data Factory

Hope it helps. Kindly accept the answer by clicking on Accept answer button. Thankyou