By: Scott Murray | Updated: 2017-09-28 | Comments (10) | Related: More >Microsoft Flow

Problem

In your SQL query run CONVERT (VARCHAR (50), startdate, 121) for datetime or CONVERT (VARCHAR (50), startdate, 112) for just date. Then use STRING data type in SSIS variables. Your life will be so much easier in SSIS. I wouldn't worry about this conversion as long as the type is compatible. Send an email after executing SQL query when new item is added to SQL table. By Microsoft Power Automate Community. When an item is created in a SQL Server table, execute a SQL query and send an Office 365 Outlook email.

I need to integrate a workflow tool for my application. I have heard about MicrosoftFlow. How can we use SQL Server to interact with Microsoft Flow?

Feb 03, 2017 Microsoft Flow helps to read data from SQL data tables (On Premise) and create the items with the data in Office 365 SharePoint list. Sign in to Prerequisite - SQL connection is already created. Steps to create a new Flow. Of course I can link directly to the tables of an SQL server and use Transform Data. That works perfectly. But in a Flow 'Execute a SQL query' I make in a few seconds a selection of a number of tables and records with a filter. And Of course I can also use a Query on the SQL server as a dataset, but I prefer to solve it outside the SQL server.

Solution

Many folks may not have heard of Microsoft Flow, but Microsoft has been quietlyenhancing this new work flow tool. The Flow toolset is a true work flow developmenttool which can trigger a variety of tasks. These tasks range from sending emailsto texting you to inserting rows into a database including SQL Server. The flowlist of triggers and tasks is ever expanding and growing. Similar to Power BI, thedatabase and other connections available seems to grow daily. Of course, one ofthe connectors for Microsoft Flow is SQL Server. This connector is similar to theconnections 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 connectorfor on-premises database, a gateway must be installed. The gateway is the conduitbetween the local database and the Flow website and tool. We will cover the entireprocess below.

Connecting SQL Server to Flow

The first step is to sign up for a Flow account by going tohttps://us.flow.microsoft.com/en-us/.If you already have an existing Microsoft Account, you can generally use that accountand 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 andstart establishing new Flows. In this particular case, we are going to use the Microsoftconnector to SQL Server in our Flow workflow. However, before we can use Flow fora local / on-premises SQL Server, our actual first step is to download the MicrosoftOn-Premises Data Gateway from -https://powerbi.microsoft.com/en-us/documentation/powerbi-gateway-onprem/. Yourfirst reaction may be that “this is the Power BI Gateway”, and in factyou are correct. The gateway is shared with Power BI.

After the Gateway is installed, a connection to the local server needs to setup in the Gateway tool. You will need to sign in to the account used for the Flowsignup.

You will need to sign into your Microsoft Account.

Depending on the login in used, the next step may ask for your password or youmay 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 confirmationscreen 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 thatthe 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 (andnot 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 Servertemplates. As shown below, we will use a template that triggers when an email isreceived 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 wewill setup our Outlook connection.

Here is the neat thing about Flow connections. If other connections to the sametool have been previously created, even for other Flows, those connections willbe available to select from by clicking on the three-dot ellipse button. As shownin the above screen print, I have two other Outlook connectors that were previouslycreated and can be used. Additionally, you will notice a third connector exists,but has a red background exclamation point. This connector was purposely setup withan invalid user ID / Password. Clicking on the Invalid connection link allows foryou to correct the link as needed.

Using a similar method, setting up the connection to SQL Server provides previouslycreated 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-premisesgateway that was previously setup. As shown in the below illustration, we checkthe Connect via the on-premises gateway option, and then fill in the server name, databasename, user type and id, and finally select the gateway name. Note gateways are associatedwith 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.

Clicking on the “On new email” trigger task, we can customize whichemails trigger the insert. As shown below, the following items can be used to filterthe 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 thetable.

We are now ready to map the data being collected in Outlook to the SQL Servertable. First, you must select the table to insert the data into. Some fields willbe shown as default, such as the From field.

Clicking on one of the boxes to right of the field name, such as subject, providesyou with the ability to select additional content items from the email data whichcan in turn be inserted into the table. For example, I added Importance to the Subjectfield, so Subject and Importance will be inserted into the Subject field in thedatabase.

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

The Flow will now run automatically and each time an email is received into myOutlook.com email box, the appropriate details will be inserted into the selectedtable.

Querying the resulting Flow table, we now see details of each email recordedin 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 aworkflow solution with such ease is a great help to keep up with the ever-changingdata environment. Currently, SQL Server does not originate any triggers, but Microsofthas 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

Microsoft Flow Execute Sql Query

Microsoft Flow Execute Sql Query From Command Line