Unanswered

Optimal Power Automate Script for Copying Large Data Sets from OneDrive to SharePoint!

(0) Share

Report

Posted on by AV-23070458-0

We're currently working on Copying ~1.2TB of data, which consists of around 2.5 lakh files/folders, from OneDrive to a SharePoint site using Power Automate. Could anyone suggest the most effective flow script to help us achieve this task successfully?

Categories:

General topics

I have the same question (0)

All responses (4)

Answers (0)

Michael E. Gernaey 53,960 Moderator on at

Like (0)

Report
Copy link

Link copied!

@Arul0597

There are a lot of details missing to really give you a perfect answer. However, here are some suggestions

1. Create a Parent Flow that generates the list of things to copy (it doesnt do the copying)
2. Use a Do Until to enable it to not time out
3. It will use an Apply to each on the Array (or json) you generate for every file to copy and where to copy it too, or which folder the child should copy all the files from or a list of files it does
4. Use a child flow to actually move the files

This way you can turn on Concurrency in your Apply to each and copy more than 1 file at a time. Technically up to 50, but I recommend starting with a smaller number like 5-10 and moving up as you validate it doesn't cause you issues.

Also, you will most likely need to wrap your Apply to each in a Do Until, because the Do Until, while it appears to be a loop, can be set to only loop 1 time, in which case it has the Apply to each in it.

This is because you can set a timeout on the Do Until, but not on the Apply to each. Even if the timeout is in days. So your flow may not run super super super fast, due to the number of files and size, but it will keep running

Steps
1. Create a Parent flow that builds the list of things to copy
-It should build that list first
-Then start a Do Until with a condition that will NOT complete until the apply to each is done
-Make sure your do Until only loops 1 time and doesn't call your Apply to each again
-In your apply to each, do NOT use any variables, other than your list, and do not update it just loop it
-click the ... and turn on Concurrency and set it between 1-50

2. Create your Child flow that will copy a file, or a set of files even but only a limited number OR do it by Folder. FOr instance, instead of saying which files to copy, have the master flow create a list of all the Folders to copy. Then have the child, either
a) copy a single file
b) a set of files
c) a folder of files

If you like my answer, I would really appreciate if you please Mark it as Resolved, and give it a thumbs up, so it can help others

Cheers

Thank You
Michael Gernaey MCT | MCSE | MCP | Self-Contractor| Ex-Microsoft
https://gernaeysoftware.com
LinkedIn: https://www.linkedin.com/in/michaelgernaey

Was this reply helpful? Yes No
AV-23070458-0 on at

Like (0)

Report
Copy link

Link copied!
@FLMike - Appreciate your swift response. Thank you.

I'm just curious about potential limitations that might affect the outlined flow, as I've come across some restrictions in SharePoint articles.

When it comes to copying or moving multiple files in a single operation, the following criteria apply:
Total file size should not exceed 100 GB.
The number of files should not exceed 30,000.
Each file must be less than 15 GB.
I acknowledge that my initial post may lack some details. Please let me know what additional information you require to assist with refining the workflow. Below are the basic requirements for your reference:
Source: Users' OneDrive [comprising 5 root folders with 1.2 TB data and approximately 2.5 lakhs+ files/folders]
Action to be performed: Copy the 5 root folders & it's associated files/folders that are inherited
Destination: Ensure the copied data from the 5 root folders & it's inherited folders/files is available on the designated SharePoint site.
Feel free to ask for any more details needed, and I'm more than happy to provide the necessary information for crafting an effective Power Automate workflow.

Thanks, Arul

Was this reply helpful? Yes No
Michael E. Gernaey 53,960 Moderator on at

Like (0)

Report
Copy link

Link copied!

Hi @Arul0597

I would follow the stuff here
SharePoint limits - Service Descriptions | Microsoft Learn

While there are certainly suggestions etc on what/how you do your storage. A lot of it is based on many other things

Imagine you had 20million files. You access them like 20 a month, who cares.
You have 20million files and your spam accessing them, yeah its a problem.

So Volume, Velocity, average size of files, user count, peak loads etc all play into what you should do.
In some cases, including at MS, we used blobs for the actual files and SharePoint for the Metadata in lists. but other times we had to use SQL or Dataverse instead because the lists got large and slow.

I wish there was a guaranteed way, but that would take my putting on my real hat and working with you directly, but I just can't do it for everyone.

I am however very intrigued by this as I have done it many times, but I am curious to see this through.

So why don't we start up an Email Chain instead. then when we are complete you can come back here and mark it as Resolved (assuming you are happy).

If you like my answer, I would really appreciate if you please Mark it as Resolved, and give it a thumbs up, so it can help others

Cheers

Thank You
Michael Gernaey MCT | MCSE | MCP | Self-Contractor| Ex-Microsoft
https://gernaeysoftware.com
LinkedIn: https://www.linkedin.com/in/michaelgernaey

Was this reply helpful? Yes No
AV-23070458-0 on at

Like (0)

Report
Copy link

Link copied!

@FLMike - Finally, we have adapted the manual method of Copying the files from OneDrive to Sharepoint Site [with limits in place] to find the solution will take more efforts. Anyway, thanks for your support & appreciate that.

Was this reply helpful? Yes No