Need instructions to set up a flow to export data from MS SQL to SP List

(0) Share

Report

Posted on by Holly_CMS

523

Does anyone know a link of good instructions to set up a flow to export data from MS SQL to SP list? I need it asap.

I can't really find one online - just from SP List to MS SQL. And the older ones are outdated.

Thanks,

Holly

Categories:

General topics

I have the same question (0)

All responses (11)

Answers (1)

wskinnermctc 6,519 Moderator on at

Like (0)

Report

Are you using a SQL Server?
https://learn.microsoft.com/en-us/connectors/sql/

Are you able to use the Premium SQL Server connectors?
SQL Server Connector

Was this reply helpful? Yes No
Holly_CMS 523 on at

Like (0)

Report

Yes, I have that. Though I need instructions to set it up - just data from MS SQL view to a SP list. The template the Power Automate has isn't what I am looking for.

I seen the link before but I just want a How to set up a flow from SQL to SP all the way if that makes sense to you?

Thank you,
Holly 🙂

Was this reply helpful? Yes No
wskinnermctc 6,519 Moderator on at

Like (0)

Report

Just trying to confirm that you already set up the connection with the SQL Server.
SQL Server - Data Connection

Do you have something like this in your Connections dashboard?

If you already have the connector set up correctly and the ability to use the Premium connector, then I can help you get the flow to work.

Can you show me what you have for a flow that isn't working? And give me some background about what you need to do and what the flow is having a problem with?

Was this reply helpful? Yes No
Holly_CMS 523 on at

Like (0)

Report

Yes, I did 🙂

I haven't set up a flow yet. I am looking from start to end. I have the SP list set up for the data to be imported (or update) from a view in SQL Server. I just need to know how to. Pretty straightforward. And then once I get the feel for it, then I can reconfig or do more with the flow.

🙂

Was this reply helpful? Yes No
Verified answer

wskinnermctc 6,519 Moderator on at

Like (2)

Report

Using the SQL Server connector is not really any different than using Get Items from SharePoint or List Rows in a Table from Excel.

Use the "Get Rows (V2)" action from the SQL connector. And then select the table/view that you want. Add any filters to the Get Rows or limits.

Then for each row you can use an Apply to Each to do what you want with the data.

Here is a very basic example. This flow will get the rows from the SQL Server view "q_PERSON_EMPL_PUB_NAMES" and then add a new row/create item in a SharePoint list.

Create SP Items from SQL Server

HOWEVER! There is a row limit just like SharePoint Get Items is 5000, the SQL Get Rows has a limit and will require pagination. I don't know if it is a row limit exactly, or if it is based on the Size of the data. Like I just tested it and could only get 2048 rows from SQL without using any pagination settings.
When I turned on Pagination in the settings and put the threshold up to 100,000; I could get up to the 100,000 limit. (It took 6 minutes to load the 100,352 rows in the Get Rows V2 action.)

One More Thing! Views Vs Tables!
Views may act like tables, but they aren't. This will be an issue when using pagination. It is possible that when using pagination on a view that a row of data could be pulled more than once.
So if you are going to be pulling in more than 1000 rows from SQL and trying to create and update data, you need to put some checks within your flow to prevent duplicates.

Was this reply helpful? Yes No
Holly_CMS 523 on at

Like (1)

Report

Got it!! On it and I will let you know!

As always, thank you 🙂

Was this reply helpful? Yes No
Holly_CMS 523 on at

Like (1)

Report

I apologize for taking so long as we had an issue with the login....It works now 🙂

Thank you!!!

Was this reply helpful? Yes No
Meyer Johannes 11 on at

Like (0)

Report

Hi wskinnermctc, thanks for the explanation and the extra tip for the views.

Working with pagination and views, you say there is chance to pull a record more than once, is there a possibility to miss a record?

Do you think it is recommended to put the Get Rows (V2) in a do-while-loop with skip-count and the ongoing check if there a more records after the first/second/third/... 100.000 records?

Thanks

Was this reply helpful? Yes No
wskinnermctc 6,519 Moderator on at

Like (1)

Report

Yes you can miss records, but I think it is rare since you will more likely get duplicated records. Probably depends on what is arranging the data in the view. I don't have a for sure way to determine what will happen.

I use the process of a Do Until that keeps putting data into an array variable and then skip counts on the next loop of the Do Until. It is basically the process shown by @takolota in his Batch Update SharePoint List instructions. I just adjusted it some because he used Excel and I'm using SQL server. But I don't really expect more than 10,000 items to ever be in my view/table.

However, I don't know the amount of data you can keep loading into an array. If you have 100,000's of records, I think it could be a problem. (Or it could be my problem since I'm putting everything into a Compose with a Union and then putting it back into an Array variable.) You can look at PA Flow Limits that microsoft posted and see if that gives you any insight.

With my process, I wasn't really tracking if rows got skipped or missed because I'm basically updating a name roster each day. So it is not exactly time sensitive and any missed records would eventually get updated. I know that is not a concrete approach, but it worked for when I set this up like over a year ago.

Another approach if you need more precision would be to make an actual Table in your SQL server. Then use the flow to call a stored procedure to update the table using your view. Then call that table with a Get Rows. This would be more solid than Get Rows using a view.

Was this reply helpful? Yes No
takolota1 4,974 Moderator on at

Like (2)

Report

@wskinnermctc

Yeah many Power Automate actions have a 100MB limit which is usually between 70,000 & 200,000 items for most table pulls.

That’s why in some of my templates when I need to reference / check over all items for a table, I’ll narrow things down as much as possible, like in the batch SP template where I just pull the ID column & any relevant primary key columns when checking which items exist / have an ID to update on. Since that only involves 2-3 columns, it can likely get up to like 1 million+ items before exceeding the 100mb limit.

Was this reply helpful? Yes No