How to query on-premises SQL Server database using power automate varible?

Question

I am trying to query an on-premises SQL Server database using a power automate variable.

Is there any suggestions or is this even possible for the time being?

I have attempted the following:

1. Using “Execute a SQL query (V2)” with an on-prem gateway connection. Not supported!
2. Using “Execute stored procedure (V2)” This won't return a value or allow variables.
3. Using “Power Query” This has similar issues to 2, that it won't allow power automate variables.
4. Consider Using Azure Managed Instances and linking the on-premises db to this instance, but can't see a obvious way for Azure to communicate with the on-prem SQL db.

Query Example: SELECT * FROM Customers WHERE Country={Power-Automate-Varible};

Answer 1

TLDR;

Flow variable name: TestVar - Type String

Table.SelectRows(#"Navigation 1", each [username] = "@{variables('TestVar')}")

"@{variables('TestVar')}" is the crux here.

It will show the preview as literal string of "@{variables('TestVar')}" during design time and thus return no relsults on your filtered column. But it'll work during runtime.

Sometimes after running and editing the flow again, the "@{variables('TestVar')}" can disappear. Just re-enter it and it'll be fine.

Original Answer:

I found a very nice solution to this problem provided by (Twitter @AymKdn / GitHub @Aymkdn) in his blogpost power-automate-execute-a-sql-query-via-on-promise-gateway.

I will quote the solution 1:1 from the blogpost, to be persisted here. All credits go to @Aymkdn.

In Power Automate, when you want to connect to a SQL Server and if you have a On-Promise Gateway, then you cannot use the command « Execute a SQL Query » because it will say it’s not currently supported.

There is a workaround with « Transform data using Power Query » (ATTENTION: you cannot load it from a flow from a Solution… you’ll have to go to your Flows and edit the flow from there):

Let’s say we have 3 tables: ITEM_CATALOG, CATALOG and CURRENCY. We want to join them and filter them based on a variable found previously in our flow.

First, we can define our where. Here I have several values that I want to test using a IN:

I create a string with my different values separated by a comma.

Next, we can open the Power Query editor:

In the interface, we choose the 3 tables we need to merge and we add a parameter called « where »:

We rename it to « where » and leave the default settings:

Then we use the « Advance Editor »:

And we write the below:

let
  where = Text.Split( "@{variables('where')}" , ",")
in
  where

It means we want to split the variable « where » coming from the flow, based on the coma separator:

We can now merge the tables and add a filter:

And when the step to filter is here, we select « in » and our query:

Last step is to « Enable Load » to make sure this is what the operation will return to our flow:

You can run it to test and see if it works.

Then, to get the output from it, we’ll use a « Parse JSON »… The schema is probably something like:

{
    "type": "object",
    "properties": {
        "resultType": {
            "type": "string"
        },
        "value": {
            "type": "array",
            "items": {
                "type": "object",
                "properties": {
                    "COLUMN_A": {
                        "type": "string"
                    },
                    "COLUMN_B": {
                        "type": "integer"
                    },
                    "COLUMN_C": {
                        "type": "string"
                    }
                },
                "required": [
                    "COLUMN_A",
                    "COLUMN_B",
                    "COLUMN_C"
                ]
            }
        }
    }
}

You may need to make several tries in order to find the correct schema. You can also use the « Generate from sample » by pasting the data from the previous step:

We use « value » in the loop:

And then we can access our columns:

Answer 2

This can be achieved by utilising Power Automate and Desktop Flows together. The limitations prevent on-prem SQL to be queried directly using a cloud flow, but querying it locally and passing the variables between Cloud and Desktop as Input and Output variables defined in the Desktop Flow.

Once defined the input variables in Desktop Flow, you can setup a run desktop flow in the cloud flow to execute the command on a local machine either attended or unattended and pass the query results back using output variables and this would automatically be stored in the cloud flow once the desktop flow has completed.

Here is further detail regarding input/output variables: https://learn.microsoft.com/en-us/power-automate/desktop-flows/manage-variables

Answer 3

As per the known limitations, querying on premise through Powerapp variables isn't possible.

However, as an alternative you can try migrating your on-premise data to Azure SQL Server and then you can query using Execute a SQL query (V2) with Powerapp variables.

Migrating steps :-

Navigate to your required database >> Tasks >> Extract Date-tier

To import navigate to your database >> Import Data-tier Application.

Below is the flow of my Logic App and the query I'm using

SELECT * from dbo.ReproTable where Country = '@{outputs('Compose')}'

RESULTS: