In BizTalk Server, SQL Adapter is the main component in order to communicate with the SQL Server. On the other hand, In Logic App we use connector to communicate with the SQL server. In this tutorial we will create a table in SQL Azure and perform read and write operations using logic app.
- Azure subscription, if you don’t have a subscription, you can start with the free subscription here .
Create table in SQL Azure:
- Login to Azure Portal.
- Click on SQL Database > Add.
- Enter database name and select your subscription and resource group. Select Source as a Blank Database and click on Server. A new section named “Server” would be appear. Click on Create a new Server or you can select existing server.
- In case of New Server, Enter any name in the Server name field and Server admin login field. Type any password and click on select. Please remember these credentials for now.
- Enter on Create button a new database would be created
- Click on your database > Overview > Tools > Query editor(Preview) and execute following query in the query window.
CREATE TABLE ingredient ( ingredientId INT primary key IDENTITY(1,1) NOT NULL,
ingredientName VARCHAR(50) NOT NULL,
ingredientPrice INT NOT NULL
INSERT INTO ingredient
( ingredientName, ingredientPrice)
Note: If you are unable to the execute query then click on Login on the top of query window and provide your credentials which we created earlier.
- Create another table with the same structure. So we can dump data from the ingredient table to a new table.
Table must have an Identity column.
Logic App to import data from one table to another table:
- Go to the Azure portal again.
- From the left azure menu, choose New > Enterprise Integration> Logic App.
- Add your Logic App name and select your subscription. Add new or use existing resource group. Resource group helps you to manage all the related resources in one resource group. Mark pin to dashboard if you want and click on the create.
- Select a blank logic app from templates.
- Type SQL as a filter in search box and select SQL Server – When an item is created
- If you are not connected already, click on Manually enter connection information. Enter all the connection information like undermentioned in figure.
- Connection Name is just like connection string name. You can use the same connection in other logic apps.
- Once you entered the information, click on the create button.
- Now choose an action to dump data to new table. At the bottom of trigger, choose + New Step > Add an action.
- Provide the table name, and set the interval and frequency for recurrence. In the screenshot I have set an internal of three minutes.
- Type SQL as filter again, and select SQL Server – Insert row from action list.
- Select table name. All table columns would be shown as text boxes.
- Set value to text boxes, you can add static or dynamic values from previous actions.
- Now you can run this Logic App.