How to get data from SQL Server to Dataflow via Power Automate

Power Automate
1

Hi,

At this moment, I am having following steps like this:

Step 1: Query yesterday data from a SQL Server. It looks like that:

SELECT
    ImageSawStepID,
    ImageID,
    SawSkillID,
    ProductionWorkerID,
    WorkingTimeInMilliseconds,
    LastModifyDatetime
FROM RemoveBackground.dbo.ImageSawStep
WHERE CONVERT(DATE, LastModifyDatetime) = '2023-06-06'
AND WorkingTimeInMilliseconds IS NOT NULL

Step 2: Then, I export to CSV file

Step 3: Then I append the CSV file to existing table in Power Query Dataflow to analyze data

These 3 steps repeated day by day

I want to automate this process.

Can I do this in Power Automate? Please show me

The prompt I use in ChatGPT : “how to get data of yesterday automatically from SQL Server, and appending to DataFlow Power Query via Power Automate”

But it does not show detail or correct steps.

Please help

Thank you

2

Hi @hunghahuy - First and 2nd step can be automated. What are you doing in 3rd step, are you uploading the csv File to DataFlow and appending it to existing table. If yes, then don’t think it can be automated.

What can be done is to maintain a Csv file that have historic data. Daily append the new data in the Csv file using Power Automate. Use this csv as source for Dataflow that can be automated by using Normal refresh process.

Do explain if you are doing something different in 3rd step.

Thanks
Ankit J

1 Like
3

Thanks @ankit ,

In Step 3, I just found another option according to your advice.

I will keep the CSV file created from step 2 as source, but will keep them in a sharepoint folder. It means there will be new csv file daily created and save in the sharepoint folder.

then I can use that sharepoint folder as a source to combine all the csv files and load to dataflow, then keep refreshing dataflow, dataset from that daily.

Following this means that the 3rd step can be broken down into small steps (3.1 and 3.2, etc)

With this option, do you think I can automate the whole process using Power Automate.

If yes, could you please show me the detail steps. I am still getting stuck in step 1 when using Power Automate

Thank you

4

@hunghahuy - Yes. That is also an option. Will suggest to raise a new post so someone expert in automate can help you.

I have limited hands on experience on flows.

Pls mark the post as closed if no further query on the process.

Thanks
Ankit J

5

Hi @hunghahuy

Just following up if you still require assistance to solve your inquiry.

In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

6

Hi @hunghahuy

Due to inactivity, we’d like to conclude that you no longer require assistance to solve your inquiry.

If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.