Synchronising SharePoint Online lists with SQL

[gjayne84]

Hi everyone

I'm after a bit of advice on the best ways of getting external data into SharePoint Online.  Currently we have users that use mobile devices to write into an on-premise SQL database but we want office staff to access this data through SharePoint.  Currently the requirement is that this a one direction write from SQL to SharePoint and any changes done on the list items in SharePoint are not synchronised back to SQL.  Eventually however we'll need bi-directional sync.

My first thought was to use BCS but from what I've seen recently this seems to be becoming legacy technology with no future roadmap from Microsoft.  A bigger problem though is the fact that PowerApps and Flow can't access the external data fields created by BCS.

At the moment I'm struggling to find any obvious alternatives to BCS apart from maybe SSIS or using Azure Function Apps and Flow to write to SQL.  The other alternative is then maybe some third-party software?  In the near future we're also looking to migrate from on-premise SQL to Azure SQL so any solution would need to be compatible with this also.

I'd be really grateful for any advice from anyone who's done something like this before and has any ideas for how I can achieve what I need.

Thanks in advance for your help.

[#2]

@gjayne84  As of now Flows for external list are not available. Use Flows with sql for notifications. We can create external list with BCS

[#3]

Thanks for the reply. As mentioned in my original post I've looked into BCS but this is no good as I can't then work with these external lists or external fields using PowerApps and Flow so this is a no-go.

I've looked into this a bit more and I think one way may be to connect PowerApps directly to our Azure SQL databases. We did want to display data on pages in SharePoint so I suppose we could then embed PowerApps into pages to display data as well as edit, rather than displaying data in lists and list view web parts in SharePoint.

[#4]

@gjayne84 

You might be able to use flow. I remember doing this once using the Data Gateway. Since I didn't have many records I just deleted all the items in the SharePoint list and then added new ones based on a SQL query from an on-premise SQLServer database. 

I think I thought of a couple of ways to get only new or changed records but, for what I was doing, deleting and recopying the records was fine and much quicker.