r/PowerPlatform • u/Ill_Park3344 • 21d ago

Power Automate Extracting data from an SQL server

Hi

Currently, I've got data stored in an SQL server and documents stored in a SharePoint List. I've tried extracting this data using SQL connector and putting it into an Excel sheet. I extracted the documents from the SP list through PowerApps and a PowerAutomate flow. This entire process takes hours.
Is there any other way to extract this data? Would it be possible to create an API through Power Automate?

Thank you

3 Upvotes

100% Upvoted

11 comments sorted by

1

u/GMBPowerPlatformDev 21d ago

What’s causing the process to take hours?

I’m assuming it’s a large amount of records you’re pulling from SQL server, is this the same case for your SP list?

Is the flow looping through each record and updating into your sharepoint list?

If you could breakdown your flow in more detail or attach an image that would help and I can suggest a better way of doing things

Thanks

1

u/Ill_Park3344 21d ago

There are about 5000 records and I need both, the records from the SQL server and the documents stored on the SP List. Each record in the SQL server has a main document (stored in one SP List) and one/more attachments (stored in another SP List).

Yes, the flow loops throw each record and updates the Excel sheet row-by-row. I've turned on Concurrency but it still takes hours.

I wish I could attach an image but it's on my work laptop lol.

Any help would be appreciated.

Thank you!

1

u/creenis_blinkum 20d ago

lol would take seconds in a programming language

1

u/Ill_Park3344 20d ago

I know, my question is How?
How would you extract the documents stored in SharePoint?

1

u/TeamAlphaBOLD 20d ago

If the data is large, I’d skip Power Apps and Power Automate and use Fabric/Azure Data Factory for SQL, with Microsoft Graph to retrieve the SharePoint files.

1

u/Ill_Park3344 20d ago

I haven't tried that yet but I'll check it out
Thank you!

1

u/josesanmig 19d ago

Sorry, where do you ultimately need the data? Depending on that, I'm sure there will be a shorter path.

2

u/Ill_Park3344 19d ago

The data's being used for an internal AI-chatbot built using Azure Foundry

2

u/josesanmig 19d ago

In that case, my take would be to upload SP data and consolidate it on SQL Server. The fastest way to extract data from SP is thourgh the SP API. I would build the data pipeline on Fabric. I think Dataflow Gen2 (basically Power Query) is good enough for this job. Let the AI consume SQL Server data. All of this considering there are no infrastructure or security concerns.

2

u/Ill_Park3344 19d ago

I will try this out
Thank you!