Within part 2 of this series (click here for part 1), I would like to cover some of the available configuration options of the Azure API SQL Server connector. The connector simply enables you to connect with a SQL Server (as well as on-premises instances) or Azure SQL Database and perform various actions. This includes; executing stored Procedures, Poll data (triggers) and the option to retrieve, create, update and delete database table entries without having to write SQL.
ATTN: This only applies for workflows created with the schema “2014-12-01”. Workflows created with a newer schema version will be using different connectors, syntax and a new designer. Additional information can be found here.
SQL Server Connector – Configuration samples
Let’s get stared by covering the configuration options however, first make sure you have a SQL Azure database available for testing purposes. In addition, verify that you are able to connect using SQL Server Management Studio by adding your current IP address within the Azure SQL Server instance firewall settings. Another important setting is the Allow access to Azure services option which can be found within the Azure SQL Server instance.
Step 1- Creating new database objects
In order to proceed, make sure to create the objects below for later use:
Step 2 – Creating a new SQL Server connector
Within the Azure portal, click on the Marketplace tile. This tile can be found on left side of the default dashboard. After opening Azure Marketplace, select API Apps for listing all available options. The SQL Server Connector can be found within connectivity section or recommended API’s.
Once found, create a new SQL Server Connector within an existing App Service Plan (or create a new App Service Plan and provision some Azure API Apps). Open the Package Settings blade and provide the Azure DB connection details within the upper part as displayed within the image below.
The interesting part can be found within the bottom section of the configuration blade. In this part make sure to provide the following data and create the connector. I will explain how you can interact with the results within the next step.
Tables: Test.CustomerA
Stored Procedures: Test.GetCustomers
Poll Data Query: SELECT c.CompanyName FROM Test.CustomerA AS c WHERE c.NeedToContact = 1
Step 3 – Creating a new Azure Logic App
Create a new Logic App and make sure it’s located within the same App Service Plan or you won’t be able to use the SQL Connector (This is a common mistake, so make sure to double check when Azure API’s aren’t available within your Azure Logic App).
Triggers – Poll Data Query
You will be able to implement triggers by adding the SQL Connector as the first action within your Logic App . During the configuration steps, you will be prompted to select a XML or Json as the result set and in addition the Poll frequency. After completing the configuration steps a new section should appear within the bottom part of the connector (see image below). This section will display the format of the message body as provided earlier within the Poll Data Query textbox.
SELECT c.CompanyName FROM Test.CustomerA AS c WHERE c.NeedToContact = 1
Table CRUD operations
CRUD operations are available for every Table you provide within the SQL Connector configuration setup. I’m using some hard-coded values in my samples to keep everything simple, but the data can obviously come from different actions within your Logic App workflow.
NOTE: It’s required to add an action block or select Run this logic manually before being able to use the SQL Server Connector Table CRUD operations.
In most cases you just have to provide the data and a where clause which is very simple process given that the interface will generate input fields as shown below.
Insert a new record
Specifying a where clause is very simple as demonstrated within the sample below. I won’t be covering the Update and Select operations because they are very similar of what we have seen so far.
Delete a record
Stored Procedures
When it comes to stores procedures, it’s important to know that only the Return Value and Out Parameters can be consumed within your logic app. This makes perfect sense given that the metadata needs to be generated based in a contract.
In the case the stored procedure is returning records of data, the output will be ignored as can be seen within the output message.
The image below demonstrates how to execute our sample stored procedure called GetCustomers. After saving and executing the workflow, the Output value should be visible within the output message generated by the SQL Server Connector (see image). The return value can be consumed within a follow-up action within your Logic App workflow.
Step 4 – Additional logic / Success and Failure logic
Within this section I want to add one more important topic; Error handling. From time to time things will go wrong and you will have to design your workflows accordingly.
I’ve covered most of the details within a previous blog post called “Azure Logic App – Conditions: Success and Failure” and can be found here
I hope this was informative for some of you!
Recent Comments