Unanswered

Getting data into SharePoint from SQL

(0) Share

Report

Posted on by mick_swartz

184

So, I know this isn't directly PowerApps related, but the entire reason I have this problem is because of PowerApps, so figured I'd try here...don't really know where else to ask.

Because of the Azure/SQL premium licensing, all new apps we are creating are connected to SharePoint. However, we are running Dynamics AX 2012 - so SQL. Does anyone know of a good way to get data into SharePoint from SQL programmatically? Our table is ~100k rows when we pare it down to exactly what we need, and would like to update it nightly.

Attempted Methods:

SSIS - Maxes out at ~20k rows before failing

CSOM/PowerShell ~ 2 seconds per record

Access Linked DB Macro - Maxes out at ~50k rows before failing

Anything to check on those methods if they should be working would be appreciated too! Been banging my head against the wall on this for a while and can't seem to find anything else on Google...

Categories:

General Questions

Connecting to Data

I have the same question (0)

All responses (3)

Answers (0)

Jeff_Thorpe 6,085 Super User 2024 Season 1 on at

Like (1)

Report

Re: Getting data into SharePoint from SQL

We use PowerShell for updating our SharePoint lists with SQL data. If your SQL was in the cloud you could also use Logic Apps but it cost extra. Technically you could use Logic Apps for SQL on-prem but then you would have to stand-up a data gateway to get to your on-prem data.

Was this reply helpful? Yes No
mick_swartz 184 on at

Like (0)

Report

Re: Getting data into SharePoint from SQL

Yeah data is on-prem. Honestly at this point I just want something that can run overnight without issues to update. Is the gateway the same thing that's used for Power BI?

What's the performance like with your PowerShell script? Even if I go from SQL -> csv -> SharePoint it seems to take a couple seconds per record to SharePoint on average which is just too long.

Appreciate the response!

Was this reply helpful? Yes No
Jeff_Thorpe 6,085 Super User 2024 Season 1 on at

Like (0)

Report

Re: Getting data into SharePoint from SQL

The data gateway I mentioned is the Power BI data gateway. The largest SQL to SPO job we have is updating almost 200k profiles but we only have to update all the records when there is an Org change or we are adding a new property to the profile. For those big changes we just schedule it for the weekend.

Was this reply helpful? Yes No