Power Automate: execute a SQL Query via On-Promise Gateway

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 coma.

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 wrote 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: