1. Developers
  2. Walkthroughs

Connecting up Microsoft Flow, SQL and Twilio

, Senior Program Manager, Microsoft Flow, Mittwoch, 11. Mai 2016

Last week, at a customer location, we attempted to solve an advanced user scenario of triggering a Flow without a built-in trigger. More than the final solution itself, I was particularly interested in how the customer used the available triggers and actions within Microsoft Flow to arrive at the solution.

The Scenario

It all boils down to this simple problem statement- When a record gets inserted into a SQL table, notify someone with a text message. It sounds like a simple Flow, but it's a powerful capability for end-users. The crux of the problem is all about converting potent insights into immediate actions that will result in huge cost savings for the customer. There is this huge backend SQL system with lots of interesting real-time data that gets tucked away into oblivion, albeit after passing through the right analysis algorithms, but when the system provides the right signal, the relevant people are not notified, so they are unable to take the corrective action at the appropriate time.

The Problem

Flow does not yet support a ‘on new record’ trigger on SQL connections. If we wanted a text message on a SharePoint list, it would have taken less than a minute to set up this flow. However, for SQL we have to hand-craft this ‘on new record’ trigger.

The Solution

Microsoft Flow supports executing Stored Procedures on SQL backend. It turns out that the customer was a SQL guru, who was able to spin up a stored procedure within minutes. The SPROC did the heavy lifting on the SQL side and dropped a single record on a special SQL table. Here is how the problem was solved.

  1. Trigger: Add a recurrence event to act as a trigger, for e.g. every day at 9a.m.
  2. Execute stored proc action: To push to the stack i.e. the special SQL table
  3. Get row from SQL action: To pop from the stack
  4. Send message action: Use the Twilio service to send the text message
  5. Update row action: To clear the stack

This is how the Flow looks like after it is setup:

The Easy Part – Send a text message

It took us about two minutes on Twilio.com to sign up for a Twilio account, verify the phone number, set up a new incoming phone number and to pick up the API key/ secret. Setup a Twilio account, then setup an incoming phone number from https://www.twilio.com/console/phone-numbers/incoming, then go to the console – https://www.twilio.com/console/ to get the account SID and auth token.

Other Solutions that could work

Custom API

The first thought that occurred to us was to use the built-in recurrence trigger along with a custom API. Flow supports this concept of exposing a custom APIs as yet another action/trigger along with the other services that are supported. Check out the detailed steps for adding a customer API here. The proposed API will fetch the unprocessed records in the database and drop them into a specific SQL table. This is how the flow will unfold:

  1. Recurrence trigger – Every n seconds

  2. Custom API action – Call the custom API to perform this special operation

  3. SQL Action – Get Record

  4. SMS Action – Send message to the phone number

While this would be feasible to build couple of days, the custom API still would need to be written/stood-up and consumed via Flows.

Web Hooks

The other thought that occurred to us was to create an API that would act as the trigger. When the SQL trigger event occurs, the API will ping Microsoft Flow via a Web Hook. This works great for efficiency because there is no polling involved, Web Hooks are supported in Microsoft Flow and listens of a specific port for the custom event to occur. This sounded great except that, this still needs extra work to understand how to hook into the web hooks infrastructure.

Finally

It was rewarding to walk away with an actual solution to the problem, however complicated. I would like to hear more from you about your scenarios and experiences with Microsoft Flow. I am the extensibility program manager in the Microsoft Flow team. If you like to explore integration opportunities with Microsoft Flow, ranging from providing a simple #HREF link to flow.microsoft.com from your application to being able to use the full CRUD/manage operations on Flows via REST APIs from within your application reach me by emailing flowdev@service.microsoft.com