We have recently shipped, in public preview, Power Query Online integration for Microsoft Flow. Using this capability allows flow makers to transform and shape their data coming from SQL Server using Power Query Online.
Why did we build this capability?
Hi, you have to declare the formalparameter as variable within the SQL query. So: DECLARE @userid VARCHAR (10); (or whatever value type) SELECT FROM a WHERE userid = @userid This way the parameters can be passed onto the query with the corresponding variable name. Microsoft Flow Execute Sql Query On Command Prompt Microsoft Flow Sql Server Connector On a daily basis, execute a SQL query, store the results in an SFTP server in a CSV format and send an email providing details.
We built this capability for many reasons including:
- An alternative to OData which can be cumbersome to use for many of our users.
- A simpler approach to joining tables than T-SQL or writing Stored Procedures.
- Future opportunities to include additional data sources in mashups that allow makers to build data transformations across multiple data sources.
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. Sep 28, 2017 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.
Licensing
The Power Query functionality will be included in our Premium Connector offering, which requires a Flow Plan 1 or higher. Currently this is not enforced as part of the preview, but will be in the future.
Scenario
Let’s take a closer look at how we can use this new capability to enhance our data extraction capabilities.
In this scenario, we have an Apartment Rental company called Contoso Apartments. Naturally, customers will inform the main office when there are maintenance issues across their many properties. Much like any Work Order system, data is organized in multiple tables including Customers, Apartments and Work Orders. The Customer Service representatives are very interested in keeping their customers happy and want to proactively ensure that customers are content. If they are not, they want to quickly course-correct to avoid them leaving to live somewhere else.
We can monitor customer sentiment by ‘mashing’ up our Customer, Apartment and Work Order data from Power Query. When we do this, we have aggregated data that can be passed to a sentiment analysis tool. When we detect that a customer is unhappy, we can then publish a message to Microsoft Teams where a customer service rep can follow-up with the customer.
To build this solution we will perform the following:
- We will add a Recurrence trigger that will run every day
- Next, we will locate our Transform data using Power Query action which is provided as part of the SQL Server connector.
- To connect to SQL Server, we need to create a Connection that includes a SQL Server name, Database name, Username and Password.
- With our connection established, we can now create a Power Query query.
- Our next step is we need to select the tables that we would like to include in our mash-up. In this case we are going to select Customers, Apartments and Work Orders.
- We want to join these different tables so that we have enriched Work Order data that includes Customer and Apartment related data for our Work Orders. To do this, we will click on Combine tables and then select Merge queries as new.
- When we merge, we need to select the type of join that we want to use. In this case we will select a Left Join and declare our Work Order table as our core table that we want to enrich with Customer and Apartment data.
Note: In this scenario, I ran the Merge twice. Once with Work Orders and Customers and then once again with my (WorkOrders and Customers) + Apartment.
- Once we have merged our tables, we can now trim our dataset by only including the columns that we need.
- Before we configure the rest of our Flow, we do need to declare our new aggregated query as the query we will Enable Load for.
Note: At this time, you can only enable 1 query that will return back to Flow. However, as we have discovered we can merge multiple queries into a single query for our use.
- With our Power Query query configured, we can now use the result set and dynamic content, much like we can do with other connectors. In our use case, what we will do with our result set is loop through each record returned and send the Work Order Comments from the customer through the Azure Cognitive Services sentiment analysis API.
- Next, we will evaluate the sentiment returned to see if it is less than .4 (which is really bad). When this occurs, we will add related Apartment, Customer and Work Order information with this sentiment value and add to an Array. After we have iterated through all of these recent Work Orders, we will then check the length of the array to see if we have records. If we do have records, we will convert this Array to an HTML table which we can then publish to a Microsoft Teams Channel.
Testing
We can now go and run this flow from within the Microsoft Flow maker portal or by calling it from the Microsoft Teams Flow Bot. Once the flow runs, it will publish the results of our flow in Microsoft Teams. This allows the Customer Service channel to target customers who are unhappy without performing a lot of data exploration.
Features
- Only SQL Server is supported as a data source. This is deliberate in our first release as we do not want to expose additional data sources that are not protected by Microsoft Flow Data Loss Prevention (DLP) policies. We do want to include additional data sources, but those will be future investments.
- We do throttle Power Query Online usage based upon:
- 2 Hours/Day
- 10 Hours/Week
Microsoft Flow Execute A Sql Query Results
This is based upon the amount of time it takes for your Power Query queries to execute. If these values don’t work for you, we would love to hear what they need to be.
- As described previously, we will only output 1 query. To avoid unexpected results, ensure that you Enable Load on the desired query.
What’s Next?
Working with the Power Query team to unlock this capability has been really exciting. Both teams see an opportunity to empower Power platform users to do more using these technologies. Since Power Query is a very rich and deep platform, we would love to hear more about Power Query + Flow use cases that you envision. This feedback will help us prioritize future investments. Please comment below.
Last Friday while checking the Twitter I found a twitter from Jon Levesque asking if someone had a sample on how to took SQL data table results and passed a Markdown-formatted table into an Approval Request. By coincidence, two weeks ago, I was helping a colleague of mine doing something similar, in that case, it was just adding a well-formatted table with the results of an SQL expression to a notification email. Two different but similar scenarios.
To be able to create this simple proof-of-concept you should:
- Access the Flow portal: https://flow.microsoft.com/ and sign in with your account
- In flow.microsoft.com, select “My flows” in the top navigation bar, and then select “+ Create from blank” to create a Flow from scratch.
- On the “Create a flow from blank”, select “Create from blank” to start the process of creating an empty Flow
Because this is a simple POC and it supposed to be a notification email we will be using the Scheduler trigger to start the Flow from time to time. For that you need:
- On the Flow designer, on the Trigger event enter “Schedule” and select the option “Schedule – Recurrence”
- For this POC, we want to receive a notification on a daily, so on the Schedule Trigger configuration set the:
- “Interval” property as “1”
- And the “Frequency” property has “Day”
- Add the next step by clicking the “+New step” button and then choose the “Add an action” option
- On the “Choose an action” window, enter “SQL Server” and select the action “SQL Server – Execute a SQL query”
Note: This can be a different action like, get rows or execute a stored procedure.
- After you connect to the proper SQL Server and database, on the Execute a SQL query action configuration:
- On the “query” property, type
- Add the next step by clicking the “+New step” button and then choose the “Add an action” option
- On the “Choose an action” window, enter “Variables” and select the action “Variables – Initialize variable”
- On the Variable action configuration:
- On the “Name” property, type “lines”
- On the “Type” property, specify the type as “String”
- On the “Value” property leave it blank
Note: Initialize variable needs to be performed on Top Level, in other words, outside any cycle operation (Do Until or For Each). Initialize variable inside cycles are not permitted.
- Add the next step by clicking the “+New step” button and then choose the “Add an action” option
- On the “Choose an action” window, enter “Variables” and select the action “Variables – Append to string variable”
Microsoft Flow Execute Sql Query Interview
- On the Variable action configuration:
- On the “Name” property, select “lines” variable
- On the “Value” property type
Note: @{items(‘Apply_to_each’)?[‘Name’]} and @{items(‘Apply_to_each’)?[‘Nacionality’]} are inserted and selected from the list of “Execute a SQL query” action tokens
- Because we can have multiple rows retrieved from SQL Server, the Flow designer will automatically place this “Variable – Append to string variable” inside a loop that will iterate thru each SQL row.
Now that we have our SQL rows partially well-formatted in a Markdown-formatted table, what we need to do is create our notification/Approval Request email. To do that we need:
- Add the next step by clicking the “+New step” button and then choose the “Add an action” option
- On the “Choose an action” window, enter “Office 365 Outlook” and select the action “Office 365 Outlook – Send an email”
- On the Office 365 Outlook action configuration:
- On the “To” property, type the email for which you want to send the email
- On the “Subject” property, type the subject of the email
- On the “Body” property, type:
Note: @{variables(‘lines’)} is inserted and selected from the list of “Variables” tokens
Now the final trick for you to receive this type of email:
instead of this:
On the Office 365 Outlook action configuration you need to:
- Click on “Show advanced options”.
- And on the “Show advanced options”:
- On the “Is HTML” property, select “Yes”