Close

Logic App parameters in SQL

In a previous post, I explained how you can use Logic App parameters in ARM templates. This solution is quite complicated. You will have to parameterize your Logic Apps. At deployment, the parameters are substituted by actual values. But, If you lookup the logic app in the Azure Portal, you will notice that the value is not displayed. Just a reference to a parameter. Difficult for support engineers. How do they know the value of Logic App parameters without access to the parameter.json file?

At my current client, I saw an interesting alternative solution whereby the workflow settings are stored in a SQL Server table. You can use the SQL Connector to retrieve the values.
If you like, you can use the Compose Json (convert to Json) and Parse Json (validate Json and transform to typed object) actions next, but you don’t have to.

“Get_rows_-_destination_-_9000”: {
“inputs”: {
“host”: {
“connection”: {
“name”: “@parameters(‘$connections’)[‘sql_2’][‘connectionId’]”
}
},
“method”: “get”,
“path”: “/datasets/default/tables/@{encodeURIComponent(encodeURIComponent(‘[setup].[workflow_orchestration]’))}/items”,
“queries”: {
“$filter”: “active eq true and interface eq ‘9000’ and direction eq ‘to_asb'”
}
},
“runAfter”: {
“Set_variable_-_source_guid”: [
“Succeeded”
] },
“type”: “ApiConnection”
},
“Compose_-_destination”: {
“inputs”: {
“destination_application”: “@{body(‘Get_rows_-_destination_-_9000’)?[‘value’][0][‘application’]}”,
“destination_blob_archive_path”: “@{body(‘Get_rows_-_destination_-_9000’)?[‘value’][0][‘blob_archive_path’]}”,
“destination_blob_error_path”: “@{body(‘Get_rows_-_destination_-_9000’)?[‘value’][0][‘blob_error_path’]}”,
“destination_entity”: “@{body(‘Get_rows_-_destination_-_9000’)?[‘value’][0][‘entity’]}”,
“destination_file_path”: “”,
“destination_file_server”: “”,
“destination_folder”: “@{body(‘Get_rows_-_destination_-_9000’)?[‘value’][0][‘blob_path’]}”,
“destination_ftp_path”: “@{body(‘Get_rows_-_destination_-_9000’)?[‘value’][0][‘ftp_path’]}”
},
“runAfter”: {
“Get_rows_-_destination_-_9000”: [
“Succeeded”
] },
“type”: “Compose”
},
“Parse_JSON”: {
“inputs”: {
“content”: “@body(‘blo-asb-afas-to-totara-conversion’)”,
“schema”: {
“properties”: {
“Files”: {
“items”: {
“type”: “string”
},
“type”: “array”
},
“RecordCount”: {
“type”: “number”
}
},
“type”: “object”
}
},
“runAfter”: {
“blo-asb-afas-to-totara-conversion”: [
“Succeeded”
] },
“type”: “ParseJson”
}

If we look the SQL table, we see the following setup:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [setup].[workflow_orchestration](
[id] [bigint] IDENTITY(1,1) NOT NULL,
[active] [bit] NOT NULL,
[interface] [varchar](10) NOT NULL,
[application] [varchar](250) NOT NULL,
[entity] [varchar](250) NOT NULL,
[direction] [varchar](50) NOT NULL,
[ftp_server] [varchar](250) NULL,
[ftp_path] [varchar](250) NULL,
[storage_account] [varchar](250) NULL,
[blob_container] [varchar](250) NULL,
[blob_path] [varchar](250) NOT NULL,
[blob_archive_path] [varchar](250) NOT NULL,
[blob_error_path] [varchar](250) NOT NULL,
[file_extention] [varchar](250) NULL,
[storage_queue_s10] [varchar](250) NULL,
[storage_queue_s20] [varchar](250) NULL,
[storage_queue_s40] [varchar](250) NULL,
[environment] [varchar](250) NULL,
[ftp_interval] [varchar](250) NULL,
[connection_type] [varchar](50) NULL,
[destination_server] [varchar](250) NULL,
[destination_path] [varchar](max) NULL,
[comment] [varchar](max) NULL,
[retry] [bit] NOT NULL,
[blob_retry_path] [varchar](250) NULL,
PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO

Interface is equal to the interface number of the client. By setting Active to 0, we can invalidate the logic app. No settings will be found as active needs to be true (see where clause of logic apps connector).

In this case, the values from the database are put in a queue message which triggers other logic apps. These logic apps use the config values.

As an example:
“destination_path”: “@{body(‘Parse_JSON_-_destination’)?[‘destination_folder’]}”

Advantages of this solution:

  • You don’t have to parameterize your logic apps.
  • Deployment of logic apps doesn’t require a parameters file.
  • Parameter values can easily be visualized by querying SQL Server.