Forum Discussion
Best apporach to pull SharePoint (O365) list data into SQL?
I am looking into options to pulling (and potentially syncing) list data from a SharePoint list (on O365) into an on-premises SQL server.
Pulling from SharePoint is the highest priority need.
I have a data gateway set up and working.
What suggestions do you have?
- spucelik
Microsoft
Eric...what's the business driver for having the data in SQL?
- Reporting and moving into our DW. It is giving the form feature of SharePoint but moving the data into a strong DB for analysis and mashup with other enterprise data.
+1 for Alan's flow suggestion. With the gateway in place it's really easy to test and you can tweak based on specific requirements. Having the ability to spawn other actions based on specific criteria, data, events or report when an issue occurs are all features that make the this approach really attractive.
- I have used Flow to do this in the past to keep an audit log on a SharePoint list. As you already have a data gateway setup you can create a Connector to update your on-premises server. Originally I used an on update trigger on the list but it created a lot of Flow runs so switched to a scheduled Flow and get all list items since last schedule run using a query with datetime, then a for each to write to the database. In the get list items action, change settings to do paging if there is a chance there will be more than 500 changes otherwise you will only get the last updated.
- As an alternative to a third party product, you could create your own sync stuff where you use CSOM to get list data and write it in SQL
