By: Scott Murray | Updated: 2017-09-28 | Comments (10) | Related: > Microsoft Flow
Problem
I need to integrate a workflow tool for my application. I have heard about Microsoft Flow. How can we use SQL Server to interact with Microsoft Flow?
Solution
Many folks may not have heard of Microsoft Flow, but Microsoft has been quietly enhancing this new work flow tool. The Flow toolset is a true work flow development tool which can trigger a variety of tasks. These tasks range from sending emails to texting you to inserting rows into a database including SQL Server. The flow list of triggers and tasks is ever expanding and growing. Similar to Power BI, the database and other connections available seems to grow daily. Of course, one of the connectors for Microsoft Flow is SQL Server. This connector is similar to the connections used for Power BI and is also available for Microsoft Power Apps tool (hopefully I can do a Power App on that subject soon). In order to use the connector for on-premises database, a gateway must be installed. The gateway is the conduit between the local database and the Flow website and tool. We will cover the entire process below.
Connecting SQL Server to Flow
The first step is to sign up for a Flow account by going to https://us.flow.microsoft.com/en-us/. If you already have an existing Microsoft Account, you can generally use that account and add Flow to your account. Otherwise, you will need to create a Microsoft account. Once you have signed up and signed into your account, you are able to go ahead and start establishing new Flows. In this particular case, we are going to use the Microsoft connector to SQL Server in our Flow workflow. However, before we can use Flow for a local / on-premises SQL Server, our actual first step is to download the Microsoft On-Premises Data Gateway from - https://powerbi.microsoft.com/en-us/documentation/powerbi-gateway-onprem/. Your first reaction may be that “this is the Power BI Gateway”, and in fact you are correct. The gateway is shared with Power BI.
After the Gateway is installed, a connection to the local server needs to set up in the Gateway tool. You will need to sign in to the account used for the Flow signup.
You will need to sign into your Microsoft Account.
Depending on the login in used, the next step may ask for your password or you may be brought to your organizations login window.
Next, the Gateway must be named and Recovery Key established.
Finally, if all goes well with your Gateway setup, you will see the below confirmation screen that the Gateway is up and running.
Once the Gateway establishes the connection to your local on-premises server, we are ready to begin to create a new Flow. One very important item to note is that the Gateway needs to be up and running in order for the connection to be successful, and in order for the Gateway to be up, the machine must also be up and running (and not gone to sleep).
Flows can be created using a predefined template or completely from scratch. We will initially use a template, but the overall process is the same with a scratch version. On the Flow web site, we will first do a search for SQL Server templates. As shown below, we will use a template that triggers when an email is received in an Outlook.com account.
The template provides us with the basic outline for the Flow tasks and triggers.
Next, the connections to Outlook.com and SQL Server must be defined; first we will setup our Outlook connection.
Here is the neat thing about Flow connections. If other connections to the same tool have been previously created, even for other Flows, those connections will be available to select from by clicking on the three-dot ellipse button. As shown in the above screen print, I have two other Outlook connectors that were previously created and can be used. Additionally, you will notice a third connector exists, but has a red background exclamation point. This connector was purposely setup with an invalid user ID / Password. Clicking on the Invalid connection link allows for you to correct the link as needed.
Using a similar method, setting up the connection to SQL Server provides previously created connections as a possible option or a new connection can be created. Again, these options are available upon clicking the three-dot ellipse button.
We will create a new connection to our MSSQLTips database using the on-premises gateway that was previously setup. As shown in the below illustration, we check the Connect via the on-premises gateway option, and then fill in the server name, database name, user type and id, and finally select the gateway name. Note gateways are associated with your login, so the gateway must use the same login as your Flow login.
Next, we need to create the table in our desired database.
CREATE TABLE dbo.Flow_Email_Tracker( Subject varchar(1024) NULL, From varchar(100) NULL, CC varchar(100) NULL, ReceiviedTime datetime NULL ) ON PRIMARY GO
Clicking on the “On new email” trigger task, we can customize which emails trigger the insert. As shown below, the following items can be used to filter the trigger:
- Email folder
- To
- From
- Importance
- Has attachment and include attachment in trigger process
- Subject
For this example, an email that arrives in the Inbox will be recorded in the table.
We are now ready to map the data being collected in Outlook to the SQL Server table. First, you must select the table to insert the data into. Some fields will be shown as default, such as the From field.
Clicking on one of the boxes to right of the field name, such as subject, provides you with the ability to select additional content items from the email data which can in turn be inserted into the table. For example, I added Importance to the Subject field, so Subject and Importance will be inserted into the Subject field in the database.
Finally, you can click Save flow to save this flow.
The Flow will now run automatically and each time an email is received into my Outlook.com email box, the appropriate details will be inserted into the selected table.
Querying the resulting Flow table, we now see details of each email recorded in the table.
This setup could definitely be used to track emails sent into an organization, such as a call or contact center. Being able to have SQL Server interact with a workflow solution with such ease is a great help to keep up with the ever-changing data environment. Currently, SQL Server does not originate any triggers, but Microsoft has requested feedback on what triggers should be established first at - https://go.microsoft.com/fwlink/?linkid=837836.
Next Steps
- See how Power BI works with Flow
- Power BI Features for Notifications - https://www.mssqltips.com/sqlservertip/4915/power-bi-features-for-notifications/
About the author
Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2017-09-28
