Azure Data Factory and SharePoint

Rather than directly accessing the file in SharePoint from Data Factory, you might have to use an intermediate technology and have Data Factory call that. You have a few of options:

1. Use a Logic App to move the file
2. Use an Azure Function
3. Use a custom activity and write your own C# to copy the file.

To call a Logic App from ADF, you use a web activity. You can directly call an Azure Function now.

We can create a linked service of type 'File system' by providing the directory URL as 'Host' value. To authenticate the user, provide username and password/AKV details. Note: Use Self-hosted IR

You can use the logic app to fetch data from Sharepoint and load it to azure blob storage and now you can use azure data factory to fetch data from blob even we can set an event trigger so that if any file comes into blob container the azure pipeline will automatically trigger.

You can use Power Automate (https://make.powerautomate.com/) to do this task automatically:

1. Create an Automated cloud flow trigger whenever a new file is dropped in a SharePoint

2. Use any mentioned trigger as per your requirement and fill in the SharePoint details

3. Add an action to create a blob and fill in the details as per your use case

By using this you will be pasting all the SharePoint details to the BLOB without even using ADF.

My previous answer was true at the time, but in the last few years, Microsoft has published guidance on how to copy documents from a SharePoint library. You can copy file from SharePoint Online by using Web activity to authenticate and grab access token from SPO, then passing to subsequent Copy activity to copy data with HTTP connector as source.

I ran into some issues with large files and Logic Apps. It turned out there were some extremely large files to be copied from that SharePoint library. SharePoint has a default limit of 100 MB buffer size, and the Get File Content action doesn’t natively support chunking.

I successfully pulled the files with the web activity and copy activity. But I found the SharePoint permissions configuration to be a bit tricky. I blogged my process here. You can use a binary dataset if you just want to copy the full file rather than read the data.

If my file is located at https://mytenant.sharepoint.com/sites/site1/libraryname/folder1/folder2/folder3/myfile.CSV, the URL I need to retrieve the file is https://mytenant.sharepoint.com/sites/site1/libraryname/folder1/folder2/folder3/myfile.CSV')/$value.

Be careful about when you get your auth token. Your auth token is valid for 1 hour. If you copy a bunch of files sequentially, and it takes longer than that, you might get a timeout error.