ARM Template for Azure Logic Apps - SQL Create Trigger

Question

Is it possible to build an ARM template for a logic app that triggers on-create of a record in SQL?

I have tried various approaches and the problem I always run into is that the connection cannot be found.

I've already created the connection in Azure and proved it works by creating a logic app manually via the portal using that connection.

This is my latest implementation (hard-coded for now):

{
      "type": "Microsoft.Logic/workflows",
      "apiVersion": "2017-07-01",
      "name": "Data-Sync-Scheduler",
      "location": "[parameters('location')]",
      "properties": {
        "state": "Enabled",
        "definition": {
          "$schema": "https://schema.management.azure.com/providers/Microsoft.Logic/schemas/2016-06-01/workflowdefinition.json#",
          "actions": {
            "HTTP": {
              "inputs": {
                "method": "GET",
                "queries": {
                  "scheduleId": "@{triggerBody()?['ScheduleId']}"
                },
                "uri": "<MY URL>"
              },
              "operationOptions": "DisableAsyncPattern",
              "runAfter": {},
              "type": "Http"
            }
          },
          "contentVersion": "1.0.0.0",
          "outputs": {},
          "parameters": {
            "$connections": {
              "defaultValue": {
                "sql": {
                  "connectionId": "/subscriptions/<ID>/resourceGroups/AZJACK001/providers/Microsoft.Web/connections/sql_2",
                  "connectionName": "sql_2",
                  "id": "/subscriptions/<ID>/providers/Microsoft.Web/locations/centralus/managedApis/sql"
                }
              },
              "type": "Object"
            }
          },
          "triggers": {
            "When_an_item_is_created": {
              "inputs": {
                "host": {
                  "connection": {
                    "name": "@parameters('$connections')['sql']['connectionId']"
                  }
                },
                "method": "get",
                "path": "/datasets/default/tables/@{encodeURIComponent(encodeURIComponent('[dbo].[Schedule]'))}/onnewitems"
              },
              "recurrence": {
                "frequency": "Minute",
                "interval": 1
              },
              "splitOn": "@triggerBody()?['value']",
              "type": "ApiConnection"
            }
          }
        }
      }
    }

EDIT 1: This is an Azure SQL hosted database.

Where is the SQL server located? Azure SQL or SQL on-premise? — AdAstra

Jack Nutkins Jack Nutkins · Accepted Answer · 2019-06-27T14:50:05

The problem I had was the difference between Logic App parameters and ARM parameters, my solution was as follows:

{
      "type": "Microsoft.Logic/workflows",
      "apiVersion": "2017-07-01",
      "name": "Data-Sync-Scheduler",
      "location": "[parameters('location')]",
      "properties": {
        "state": "Enabled",
        "definition": {
          "$schema": "https://schema.management.azure.com/providers/Microsoft.Logic/schemas/2016-06-01/workflowdefinition.json#",
          "actions": {
            "HTTP": {
              "inputs": {
                "method": "GET",
                "queries": {
                  "scheduleId": "@{triggerBody()?['ScheduleId']}"
                },
                "uri": "<MY URL>"
              },
              "operationOptions": "DisableAsyncPattern",
              "runAfter": {},
              "type": "Http"
            }
          },
          "contentVersion": "1.0.0.0",
          "outputs": {},
          "parameters": {
            "$connections": {
              "defaultValue": {},
              "type": "Object"
            }
          },
          "triggers": {
            "When_an_item_is_created": {
              "inputs": {
                "host": {
                  "connection": {
                    "name": "@parameters('$connections')['sql_2']['connectionId']"
                  }
                },
                "method": "get",
                "path": "/datasets/default/tables/@{encodeURIComponent(encodeURIComponent('[dbo].[Schedule]'))}/onnewitems"
              },
              "recurrence": {
                "frequency": "Minute",
                "interval": 1
              },
              "splitOn": "@triggerBody()?['value']",
              "type": "ApiConnection"
            }
          }
        },
        "parameters": {
          "$connections": {
            "value": {
              "sql_2": {
                "connectionId": "/subscriptions/<MY ID>/resourceGroups/AZJACK001/providers/Microsoft.Web/connections/sql_2",
                "connectionName": "sql_2",
                "id": "/subscriptions/<MY ID>/providers/Microsoft.Web/locations/centralus/managedApis/sql"
              }
            },
            "type": "Object"
          }
        }
      }
    }

ARM Template for Azure Logic Apps - SQL Create Trigger

1 Answers