Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Microsoft Flow and SQL Server Integration Example


By:   |   Read Comments (9)   |   Related Tips: More > Microsoft Flow

Next Free Webcast - The more things change… DBAs versus Sysadmins in cloud availability


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.

OnPrem Gateway - Description: OnPrem Gateway

You will need to sign into your Microsoft Account.

Gateway Signin - Description: Sign in to Gateway

Depending on the login in used, the next step may ask for your password or you may be brought to your organizations login window.

Organization Login - Description: Login to organization

Next, the Gateway must be named and Recovery Key established.

RecoveryKey - Description: Recovery Key for Gateway

Finally, if all goes well with your Gateway setup, you will see the below confirmation screen that the Gateway is up and running.

Gateway Up - Description: Gateway 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.

New flow - Description: New flow with Outlook and SQL Server

The template provides us with the basic outline for the Flow tasks and triggers.

New Flow Template - Description: new Flow Template

Next, the connections to Outlook.com and SQL Server must be defined; first we will setup our Outlook connection.

Create Outlook Connection - Description: Create 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.

Add SQL Connection - Description: Add SQL Connection

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.

Link connection to Gateway - Description: Link connection to Gateway

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.

Outlook Trigger Filter - Description: Outlook Trigger Filter

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.

SQL Server Insert - Description: SQL Server Insert

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.

Outlook to SQL Server Mapping - Description: Outlook to SQL Server Mapping

Finally, you can click Save flow to save this flow.

Save Flow - Description: Save Flow and Execute

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.

Flow Results - Description: Flow Results

Querying the resulting Flow table, we now see details of each email recorded in the table.

SQL Results - Description: SQL Results

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.

Trigger Suggestions - Description: Trigger Suggestions
Next Steps


Last Update:


next webcast button


next tip button



About the author
MSSQLTips author Scott Murray Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Send me SQL tips:

    



Friday, March 16, 2018 - 11:51:03 AM - Eric B Back To Top

 Thanks 


Friday, March 16, 2018 - 10:22:01 AM - Scott Murray Back To Top

 Ok, I see what you are wanting... I do not believe you will be able to do that necessarily with the current Flow connectors. It is more individual to a single address and would be a nightmare to get everyone to setup this sort of individual connection.  One other idea may be Power BI.  

https://powerbi.microsoft.com/en-us/blog/analyzing-your-microsoft-exchange-account-data-with-power-query/ 

https://powerbi.microsoft.com/en-us/integrations/exchange/

I have not explored this option, but just throwing it out.


Friday, March 16, 2018 - 9:14:51 AM - Eric B Back To Top

 Scott,

Thank you for the response. I looked at what you sent, but that doesn't really help me out. My fault as I could have explained myself a little better. I am trying to utilize Flow to take company wide emails from Outlook and dump them into a SQL database so I can analyze some things. I am able to set up a flow to capture my emails, but I have not been able to find a way to capture emails for other users (even when I try and log in with an admin account). Just trying to figure out if this is limited in Flow and if I should try and find another way to accomplish what I am trying to do. Any suggestions would be much appreciated


Thursday, March 15, 2018 - 3:25:38 PM - Scott B Murray Back To Top

 

 

Eric,

I would check into something like this connection to gmail: 

https://us.flow.microsoft.com/en-us/galleries/public/templates/938ddaf0ebee11e68d2ddfad08ce110c/log-gmail-emails-to-an-excel-file/

 


Thursday, March 15, 2018 - 12:42:15 PM - Eric B Back To Top

 You mentioned this would be great for tracking emails into a call center or something similar. How can you set up the flow to grab emails other than your own? I have tried to login with an admin account and tried to add an additional email address of my coworker to my flow with no success. I am able to transfer my emails to a table, but want to analyze a group of emails and have not been able to figure out how to get those additional addresses to create rows within the SQL table I created.

 

Let me know what you can please. 

Thanks


Monday, February 05, 2018 - 7:32:37 PM - Scott Murray Back To Top

 One Idea I can think of is to first save the attachment to a SharePoint File Share and then use SSIS  to load the data.... maybe: https://ms.flow.microsoft.com/en-us/galleries/public/templates/f7a46809e53c42108034e56acf83bb79/save-my-email-attachments-to-a-sharepoint-document-library/

 

Another option would be to use PowerShell.... never tried this, so not sure if it works.

 

https://sqldbawithabeard.com/2013/06/20/powershell-can-read-email-insert-excel-file-attachment-into-a-sql-database/

 


Monday, February 05, 2018 - 3:05:15 PM - Steve H Back To Top

This is interesting - Flow also deals with email attachments. Do you know if there is an out of the box way to take an excel sheet delivered by email and import it into a database? We have a regularly delivered report in email that comes as a CSV - How would you go about getting that into SQL?


Friday, January 05, 2018 - 8:47:57 AM - scott mruray Back To Top

Kieran,  You would need to create that prior to adding to the gateway.


Friday, January 05, 2018 - 6:51:25 AM - Kieran Back To Top

 Hi Scott,

How would you know what your user name is when entering your sql login information?

 

Many thanks

 

 

 


Learn more about SQL Server tools