Sync SharePoint Online List to on-premise SQL Server

[John Twohig]

I would think it would be a common requirement to have lists in SharePoint contain consistent data with an on-premise source. A simple description of what I want to do is:

• I have an employee table in an ERP system with an employee number (key), name, attribute1, attribute2
• I want a SharePoint Online list with four fields - employee number , name, attribute1, attribute2
• If a new record is added in the ERP system I want a new record added to the SharePoint list
• If the employee name, attribute1, or attribute2 are changed in the ERP system I want the data for that employee changed in the SharePoint list

I haven't seen a good way to do this. In my research I have come across a few possibilities:

• BCS - It appears that this would work but, from my reading, BCS is on its way out and not really a good choice for a new application
• SSIS - I was optimistic when I first saw that the OData source would read from a SharePoint Online list. What I want to do would be simple using SSIS. However, it appears that there is no equivalent destination that could write to a SharePoint list
• Flow - I got the impression that this was the "new" "best" way to do such things. We have a Data Gateway in place and can access the SQL Server with Flow. However, Flow seems to be a very cumbersome tool to do what I want to. There doesn't seem to be a good way to check if the records returned from SQL server already exist in the list or are different and then write the changes to the list

It there some standard way to do this? Is Flow the right tool and I just have to learn more how to use it?

Thanks

[#2]

Short answer: not is not...another two options you have here:
(1) Build your own sync stuff
(2) Third party tools

[#3]

Just in case anyone else needs to do this I was able to get it to work using flow. It turned out to be very easy only taking about 15 minutes to write the flow. 

In my case what made it easy was I only have to update the list once at night and the lists are small enough ( About 1,000 list items) that I can delete all list items first and then add them again. That way I don't have to check to see which are new or which have changed.

There are only four steps:

1. Get all existing items in the list
2. Delete all the items from the list
3. Get all the rows I want from the on-premise SQL Server. There was already an on-premise data gateway in place
4. Create a new item in the list for each row retrieved

It should still be doable if you needed to update the list with changed or new items regularly during the day but would be much more complex and, in my case, not worth the effort.