Building on from the Part 1 post of a few weeks ago, let’s extend the treasure hunt with some additional functionality. To find our next set of hidden gems, let’s transform the received message after having validated it, then use the transformed message to call a web service, and finally save the result into a SQL Azure table. We also want to make sure that we save any errors in SQL Azure as well, but to a different table.
Finding More Treasure
In attempting to create the flow I described above, I ran into a number of issues with the SQL Connector API App, so I decided to dedicate this Part of the series to the use of this API App as an action. The SQL Connector can be used in a variety of ways, as detailed on the Azure site (http://azure.microsoft.com/en-us/documentation/articles/app-service-logic-connector-sql/). In this example, I am using it as an action for adding records to one of two tables in a single SQL Azure database, but some of the lessons learnt are applicable even if you intend to use this connector as a trigger.
The first lesson learnt in implementing this workflow was that, to add a SQL Connector API App to the workflow, you provide the SQL Connection details, down to the database, when adding the API App. This means that if you want a Logic App to interact with more than one database, you need to add a SQL Connector API App for each database. For this scenario, we only need one SQL Connector, which will point to a database that contains a SuccessResult table and an Error table.
The second lesson learnt was that you need to specifying the full Server name, e.g. “messagevalidationsqlserver.database.windows.net", even if you are pointing to an Azure SQL Server. When setting up the SQL Connector I initially provided the name of the SQL Server, as created in the Azure Portal, i.e. “messagevalidationsqlserver”. This resulted in the text “Error” displaying next to the name of the Connector in the API Apps listing.
The third lesson learnt also relates to the configuration of the SQL Connector. When creating the connector, specify the SQL tables or stored procedures that you intend to use the SQL Connector to interact with. You can also set these after creating the connector by going to the SQL Connector’s blade, then selecting the link for the related API App Host, and then moving on to the Hosts’ properties. Whichever way you set the table or stored procedures, if you do not set them you will get “This API App does not have any actions” text in the Logic App. This will also happen if you have not configured the SQL Connector’s connection information correctly (as above). Another impact of this is that you will not be able to Download Schemas from the SQL Connector’s blade. To be clear … the Download Schemas link will be active, but clicking the link will download a ZIP file of zero bytes.
Once correctly configured, you will see that after adding the SQL Connector to your Logic App you will be resented with a list of actions, based on the tables or stored procedure specified. In this case, the SQL Connector was configured with the SQL Tables property set as “Error,SuccessResult”, thereby instructing the SQL Connector to generate actions all CRUD actions on both of these tables:
Each Insert, Update and Select action has both a JSON and XML option. Selecting the JSON option presents you with the specific database values used to perform the selected action. Selecting the XML option presents you with a single property: the XML message that can be used to effect the selected action, the schema for which can be downloaded from the SQL Connector’s blade.
The final lesson learnt when using the SQL Connector: Use the JSON option when the values are either static or can be derived from other actions in your logic app, and use the XML option where you already have the XML in your logic app, or where you create the required XML using the BizTalk Transform Service.
Conclusion
The SQL Connector is a versatile connector that many people will want to use as they venture into the world of micro-services. Hopefully, the lessons learnt here will help you on your own journey of learning and experience:
- Each SQL Connector API can be configured to point to a single database only, although it can be used to reference multiple tables and/or stored procedures within this database.
- When configuring the database server name property of the SQL Connector, specify the fully qualified domain name for the SQL Server name, e.g. “messagevalidationsqlserver.database.windows.net”
- If you are using the SQL Connector as an action, ensure that when you add the SQL Connector API App you specify the tables and/or stored procedures that you want to use. If you need to reference multiple tables or multiple stored procedures, separate each of these with commas.
- Once everything is correctly configured, you will be able to select either a JSON or XML implementation of those actions related to the table or stored procedure you configured. Choosing between the JSON or XML implementation will largely depend on where the data to be used to perform the action is available within your logic app.
Let me know if this helps, if you have any treasure nuggets to add, or if there are other specific Logic App scenarios you would like more information on.
