
I am trying out Power Automate Flows to transfer list data from SharePoint Online to On-Prem SQL server.
Trying to compare it to what we use now which is SSIS with a paid SharePoint toolkit
Started out with a test of a small list with only 13 entries and that took 20 seconds which is not great
Next tried a list with 677 entries and 20 fields but I only mapped 4 to transfer. This thing doesn't work at all.
It gets stuck on deleting all the records from SQL first. I could see it deleted about 50 records initially then ran for 15 minutes without doing anything until I cancelled it.
If I start out with an empty table (truncating it manually) then the flow takes about 12 minutes to transfer 4 fields and 677 records.
Is this tool not designed for this or am I missing something? In SSIS this same process will run in just few seconds
I already tried enabling Concurrency Control and setting Degree of Parallelism to 50 for all 3 'Apply to Each' steps.
It seems to make things worse instead of better or no difference.
Any advice or suggestions?
Definitely is not the best solution, as power automate actions are limited or will be limited per user based on his license. If we are talking about 600 rows to add in on premise SQL database, you execute 600 actions in apply to each loop.
One solution in this case is to use azure datafactory.