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: