In this sample, we will use our fictitious company Sunny Electricals to do cognitive analysis done for a product based on the customer response once an item is sold, replaced, or serviced. We will be using the WCF-SQL adapter to poll the on-premise SQL server and get customer feedback data available for Logic App to perform cognitive analysis.
This solution requires the following components to be installed and configured along with Microsoft BizTalk Server 2016:
Azure Logic Apps provide cognitive services connector to be used in Logic App workflow. In the solution, we will show how to use Logic App that does sentiment analysis on sales data sitting on-premise. To perform sentiment analysis, we will use the Text Analytics API from cognitive services. To do this, in the Azure portal, you need to create cognitive services account:
Once cognitive service is created in Azure portal. Create a new Logic App with an HTTP request-response. This Logic App will use a sentiment analysis API over the requested data and send the response score to the caller:
The next step is to create BizTalk Project with the request response Logic App port to make a request call to the cloud-hosted Logic App endpoint. In the exercise, we will be polling the SQL server to get the latest sales data and update each record with sentiment score.
To poll for sales and product feedback data from SQL server, we will be using the WCF-SQL Adapter in BizTalk Server 2016. The adapter supports receiving polling-based messages, wherein the adapter executes a specified SQL statement, retrieves or updates the data and provides the result to the BizTalk Server receive location.
The WCF-SQL adapter supports three types of polling:
FOR XML
clauseFor the purposes of this sample, we will be using the sales table of Sunny Electricals database. The table script is shown here:
To start with, create a Visual Studio BizTalk Project and navigate to Add Generated Items | Consume Adapter Service to generate XML schema for the Typed polling, which will return sales data based on date processed state:
After selecting the sqlBinding as the adapter, populate the URI properties with server name, database name, and InboundId:
In the Binding Properties tab, set the adapter to use TypedPolling and populate the PollingDataAvailableStatement to a SQL statement that counts how many records match the Polling query. In the PollingStatement
value to write query which will return records from Sales table where the IsProcessed flag is 0:
Click on connect to make sure that the URI configuration is correct. Select Service (Inbound operations) in the dropdown for the contract type. Choose TypedPolling and click on Add while selecting Generate unique schema type:
When the wizard is completed, it will end up with one new schema (and one binding file) added to the project:
The final step is to split the polling data into separate distinct messages at the BizTalk pipeline stage. This can be done by setting the envelope property of the generated schema and the XPath of the repetitive node.
If you require more information on how you can de-batch the sql message in Pipeline, you can refer to the following blog post: https://seroter.wordpress.com/2010/04/08/debatching-inbound-messages-from-biztalk-wcf-sql-adapter/.
Create orchestration, which will trigger with the sales data and call a two-way Logic Apps endpoint to get customer feedback sentiment analysis.
Once the sentiment analysis score is received in your orchestration, call the update sentiment analysis stored procedure to update the sales table.
Once the BizTalk orchestration workflow is completed, build and deploy the application within BizTalk administration console:
From the BizTalk administrative console, navigate to the newly deployed BizTalk application. Expand the application, and on the send port, right-click to create a new two-way send port.
For configuration of two-way send port, select the Logic App Adapter as desired handler. On LogicApp Adapter Transport Properties, click on Configure. This will open a pop-up window. Enter your correct Azure subscription detailed under which sentiment analysis Logic App is deployed:
Once the BizTalk orchestration is correctly bounded with WCF-SQL and logic adapter, verify the run from the Azure portal and look at a sentiment analysis update on the sales table: