When you try to copy large files from SharePoint to Azure Bob Storage with Power Automate, you will find that you have a few options. But quite a few of them fail straight away. In this post the solution.
I’m going to include my failed attempts first. In my situation I would like to take a file in SharePoint, get its content and then upload it to Azure Blob Storage.
Attempt 1 – Get file content failure
I configured a get file content action in my flow by selecting my site and then my file.
When I run my flow I will find that I get the following error:
The solution to this problem is to use chunking. As my file exceeds to maximum amount of data that can be downloaded in one go, we are receiving this error. Some Power Automate actions will have a chunking option available. But the Get File content action does not.
The very similar error is given with the Get file content using path.
Attempt 2 – Copy files from a SharePoint Folder to a AzureBlob folder
The grammar/spelling mistakes in this template title already don’t give me much hope. But 9124 people have tried this template, so it must be doing something?
The flow that is created with this template is just useless. First of all I don’t want to copy the file on creation, triggerBody() is not giving me the content of the file.
Attempt 3 – Copy Large Files with Pnp PowerShell
When I use the following lines of PowerShell I managed to download the file
Connect-PnPOnline -Url https://pieterveenstramvp.sharepoint.com -PnPManagementShell
Get-PnPFile -Url "/Shared Documents/SSMS-Setup-ENU.zip" -AsFile -Path c:projects -Filename DownloadedFile.zip
So if we can download a file using PnP Powershell, then we can also get to the file using Azure Functions or an Azure Runbook. Or alternatively you could of course using the Core libraries that come with PnP as well, but I’m trying to keep this as low code as possible.
Configure an Azure Automation Account
I’m now going to my portal.azure.com to configure my Automation account
After a short wait the Automation Account will be ready.
Then I ‘ll create a runbook.
Install PnP PowerShell
For the full details see my post about installing PnP Powershell in Azure Automation from a few years ago.
In short download PnP PowerShell using the following line:
save-module -Name PnP.PowerShell -Path c:projects
Then upload it to your Azure Automation configuration that we created earlier.
Then within the app registration I’ve used a certificate that is created following the steps described by Microsoft in their document on Microsoft Learn.
The app registration will need
Adjust the PowerShell script to run in Azure Automate
Then we will need to adjust the PowerShell that we wrote earlier a bit. This is to ensure that we can run in silent mode.
Connect-PnPOnline -ClientId c357ce92-b22d-459e-b10f-31bf76f8629d -Url $SiteUrl -Tenant "PieterVeenstraMVP.onmicrosoft.com" -Thumbprint D153D3DC3496B33CFE94218A89BC685BE7A8752D7
Get-PnPFile -Url "/Shared Documents/Large File.zip" -AsFile -Path . -Filename "Large File.zip"
Now that we have the file content we will need to complete the next step of uploading the file to our Blob Storage.
To make the above connection work, make sure that you have supplied to App Registration with the following permission
Uploading a large file to Azure Blob Storage
The first step is to connect to Azure Blob Storage.
I was planning to use the same app registration as earlier however Azure PowerShell commands for uploading Bobs require Application permissions that don’t exist.
So this time I’m using a Managed Identity to get the job done.
$Context = Set-AzContext -Subscription "Visual Studio Enterprise 115"
$rg = "DefaultResourceGroup-CUS"
$storageaccount = "pvdemoblobstorage"
$key = (Get-AzStorageAccountKey -ResourceGroupName $rg -Name $storageaccount).Value
$StorageContext = New-AzStorageContext -StorageAccountName $storageaccount -StorageAccountKey $key
$ContainerName = "democontainer"
Set-AzStorageBlobContent -Container $ContainerName -context $StorageContext -File ".Large File.zip" -Blob "Large File.Zip"
With the above added to the script I’ve now got my large file copied from SharePoint to Azure Blob Storage.
Calling the Azure Automation Runbook from Power Automate flows
Now the easy part. Build a flow with 2 actions to copy large files from SharePoint to Blob Storage
- Create Job
- Get job Output
And now my flow will copy my large file. in about 45 seconds.