1

We worked with BCS (Business Connectivity Services) and ECT (External Content types) a while back and did not like it. Seemed like a lot of overhead maintaining the map. If the goal is to keep a single Azure SQL Table in sync with an SPO Modern List, can we just do it with Microsoft Flow and not use BCS/ECT?

A once daily scheduled Flow at 1am that synchronizes the two sources is fine. One SQL table Row to One List item, but the list has 80k+ items and we can't do full list syncs every night.

Technically seems very do-able, but wondering what challenges we will face if we skip using BCS or ECT?

Thanks.

Improve this question
2
  • What type of sync do you want to have? Is SQL table the master one in the relationship and SP list is just for reading? Changes in both sources have to be sync to each other?
    – jaloplo
    Commented Aug 12, 2019 at 21:23
  • simple single linear model, 1 row to 1 item. But the list has 80k items so we can't do full sync every night.
    – Hell.Bent
    Commented Aug 12, 2019 at 22:02

1 Answer 1

Reset to default
2

Microsoft Flow could be a good tool to export information from multiple sources like an Azure SQL database. But you should check Azure Functions and Office Graph API as well. This couldn't be a solution if you are thinking in a non code solution but I think it could be great because of all the possibilities that opens up for logging, tracing, checking and so on.

Microsoft Flow allows you as a user to create workflows but not as a system account. Think of this restriction when choosing the tool, that's an important point.

Talking about the solution:

Your solution must have a modified date for each item in the SQL database to know the las rows that have been modified. You must have a control table to save the time of the last iteration. Having these two dates you will be able to update the rows that have been affected in SharePoint. This solution didn't check the deleted rows so you should think on a better solution.

Another approach could be delete all items in SharePoint and create all new items from the SQL. The problem here will be the time on deleting and creating again all these items. The search engine will be affected as well. It will crawl the 80k items each day and update the index for those 80k items plus the 80k items removed.

Hope this can help you.

Improve this answer
3
  • We've worked with Azure Functions and Graph API as well. All great options , as well as SSIS. But just wondering if BCS and ICT are as good of an approach for keeping a SQL table in sync with an SPO Modern List?
    – Hell.Bent
    Commented Aug 13, 2019 at 14:05
  • Basically looking to tell the SPO 2019 Modern List customer who is insisting on BCS/ECT that it's not necessary and that he should build it with one of several options including Flow, SSIS, Azure Functions/GraphAPI and not be wrong about best practices.
    – Hell.Bent
    Commented Aug 13, 2019 at 14:29
  • BCS in SPO needs a hybrid environment to work properly. From that POV, it means that complicate the whole solution. The Office Graph solution takes advantage of the Rest services available and it can be scaled easily adapting to changes in the company, the complex thing here is coding. Microsoft Flow has the disadvantage that workflows must be created by a user and belongs to him. Just make a table with all solutions and rate each of them based on useful criteria. Put criteria in your favor.
    – jaloplo
    Commented Aug 13, 2019 at 14:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.