On‑Premises Data Gateway: Why the April 2026 Release Really Matters

Posted on April 30, 2026 by Salvatore Pellitteri

Usually, the release of a new On‑premises Data Gateway version goes almost unnoticed.
This time, it doesn’t.

With the April 2026 release, a feature has been introduced that can truly make a difference for anyone managing Power BI and Azure Logic Apps environments across multiple customers:

👉 controlled gateway updates.

What does this mean in practice?

Until now, updating the On‑premises Data Gateway was only possible manually and on demand.

That translates into a very concrete scenario:
if you manage multiple customers and must comply with strict maintenance windows, you’re forced to connect only during those specific time slots to perform the update—customer by customer, environment by environment.

With this new release, things change significantly.

You can now schedule the time window during which the gateway will be updated, while maintaining full control over when and how the upgrade takes place.

The result?

Fewer manual logins, fewer repetitive tasks, stronger governance, and a much more sustainable way to manage gateways supporting Power BI and Azure Logic Apps.

How to get started

Upgrade the gateway to the April 2026 version (3000.314)
Sign in as a gateway administrator
From the gateway configuration (UI or portal):
- check the current version
- trigger the update when available
Manage upgrades:
- on demand
- within controlled maintenance windows

What is not yet documented

a toggle to enable fully automatic updates
a completely unattended, Windows‑Update–like mechanism

On-premises data gateway April 2026 release | Microsoft Fabric Blog | Microsoft Fabric

How-to create Azure API Connection resource with a specific name

Posted on October 7, 2022 by Salvatore Pellitteri

In general, I am a person who gives a lot of importance to the naming convention, my colleagues make fun of me for this.

However, when I develop with Azure Logic App irritates a lot me when I create a connection and I don’t have the possibility to specify its name. So, I find connections with names like “sql”, “sql-1”, “sql-2”, etc.

I hate this thing and I must work all the time around template parameters to get API Connection resources with the names I want.

After some research I found a way to create the API Connection before starting the development of the Logic App so that I can also decide the name.

To do this, simply create a resource template and start the deployment procedure from the portal.

Below is an example of a resource template for a connection to SQL Server.

{
    “$schema”: “https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#”,
    “contentVersion”: “1.0.0.0”,
    “parameters”: {
        “subscription”: {
            “defaultValue”: “[your subscription id]”,
            “type”: “String”
        },
        “resourceGroup”: {
            “defaultValue”: “[your resource group name]”,
            “type”: “String”
        },
        “connections_name”: {
            “defaultValue”: “[your favorite name!!]”,
            “type”: “String”
        },
        “connections_display_name”: {
            “defaultValue”: “[your favorite display name!!]”,
            “type”: “String”
        },
        “server”: {
            “defaultValue”: “[SQL Server name]”,
            “type”: “String”
        },
        “database”: {
            “defaultValue”: “[SQL Server database name]”,
            “type”: “String”
        },
        “authType”: {
            “defaultValue”: “basic”,
            “type”: “String”
        },
        “username”: {
            “defaultValue”: “[SQL Server user name]”,
            “type”: “String”
        },
        “password”: {
            “defaultValue”: “[SQL Server user password]”,
            “type”: “String”
        }
    },
    “variables”: {},
    “resources”: [
        {
            “type”: “Microsoft.Web/connections”,
            “apiVersion”: “2016-06-01”,
            “name”: “[parameters(‘connections_name’)]”,
            “location”: “westeurope”,
            “kind”: “V1”,
            “properties”: {
                “displayName”: “[parameters(‘connections_display_name’)]”,
                “parameterValues”: {
                    “server”: “[parameters(‘server’)]”,
                    “database”: “[parameters(‘database’)]”,
                    “authType”: “[parameters(‘authType’)]”,
                    “username”: “[parameters(‘username’)]”,
                    “password”: “[parameters(‘password’)]”

                },
                “statuses”: [
                    {
                        “status”: “Connected”
                    }
                ],
                “customParameterValues”: {},
                “createdTime”: “2022-10-06T14:40:58.2568773Z”,
                “changedTime”: “2022-10-06T14:40:58.2568773Z”,
                “api”: {
                    “name”: “sql”,
                    “displayName”: “SQL Server”,
                    “description”: “Microsoft SQL Server is a relational database management system developed by Microsoft. Connect to SQL Server to manage data. You can perform various actions such as create, update, get, and delete on rows in a table.”,
                    “iconUri”: “https://connectoricons-prod.azureedge.net/u/laborbol/patches/1595-sql/1.0.1595.2993/sql/icon.png”,
                    “brandColor”: “#ba141a”,
                    “id”: “[concat(‘/subscriptions/’, parameters(‘subscription’), ‘/providers/Microsoft.Web/locations/westeurope/managedApis/sql’)]”,
                    “type”: “Microsoft.Web/locations/managedApis”
                },
                “testLinks”: [
                    {
                        “requestUri”: “[concat(‘https://management.azure.com:443/subscriptions/’, parameters(‘subscription’), ‘/resourceGroups/’, parameters(‘resourceGroup’), ‘/providers/Microsoft.Web/connections/’, parameters(‘connections_name’), ‘/extensions/proxy/testconnection?api-version=2016-06-01’)]”,
                        “method”: “get”
                    }
                ]
            }
        }
    ]
}

To create the resource connect to Azure Portal, type “deploy” in the “Search resources, services, and docs” text box and select “Deploy a custom template”

In the custom deployment page, click on “Build your own template in the editor”

Now click on “Load file” to load your resource template.

When the template has loaded click on “Save”.

Now you can confirm the default parameters you specified in the template or modify them.

So, click on “Review + create” and then on “create”

In a connection to SQL that requires an On-Premises Data Gateway, add the following sections to the template:

Parameters section

“gatewayName”: {
    “defaultValue”: “[your gateway name]”,
    “type”: “String”
},
“gatewayResourceGroup”: {
    “defaultValue”: “[the resource group in which the gateway is defined]”,
    “type”: “String”
}

ParametersValue section

“gateway”: {“id”: “[concat(‘/subscriptions/’, parameters(‘subscription’), ‘/resourceGroups/’, parameters(‘gatewayResourceGroup’), ‘/providers/Microsoft.Web/connectionGateways/’, parameters(‘gatewayName’))]”}

That’s it!

How to call a Microsoft Graph API from Azure Logic App

Posted on March 4, 2019 by Salvatore Pellitteri

Microsoft Graph is the gateway to data and intelligence in Microsoft 365.

You can use the Microsoft Graph API to build apps for organizations and consumers that interact with the data of millions of users. With Microsoft Graph, you can connect to a wealth of resources, relationships, and intelligence, all through a single endpoint: https://graph.microsoft.com.

For additional information refer to https://docs.microsoft.com/en-us/graph/overview link.

Before using the APIs from the Microsoft Logic App, some configurations must be made within Microsoft Azure Active Directory.

Azure Active Directory Setup

In the left-hand navigation pane, select the Azure Active Directory service.

Select “App registrations” (in preview at this moment) and then select “New registration”.

Enter an application name, select the account type you want to enable and click on “Register”

When finished, take note of “Application (client) Id” and “Directory (tenant) Id”.

To give the capability of calling Microsoft Graph API to your Logic App, you have to select the API permissions.

Click on “Add a permission”.

Select “Microsoft Graph”.

Now you have to choose the permission type, Delegated or Application.

In our case, we need to call the API listed below. Microsoft Graph API documentation specifies the permission required for each method.

List All Teams

https://docs.microsoft.com/en-us/graph/teams-list-all-teams?toc=./ref/toc.json&view=graph-rest-1.0

Clone a Team

https://docs.microsoft.com/en-us/graph/api/team-clone?view=graph-rest-1.0

Add Group Owner

https://docs.microsoft.com/en-us/graph/api/group-post-owners?view=graph-rest-1.0

Add Member

https://docs.microsoft.com/en-us/graph/api/group-post-members?view=graph-rest-1.0

So, we have granted the following permission.

Permission	Type
Directory.AccessAsUser.All	Delegated
Directory.ReadWrite.All	Delegated
Directory.ReadWrite.All	Application
Group.ReadWrite.All	Delegated
Group.ReadWrite.All	Application
User.Read	Delegated
User.Read.All	Delegated
User.Read.All	Application
User.ReadWrite.All	Delegated
User.ReadWrite.All	Application

Once the necessary permissions have been added, you will see the following result. Now you have to delegate the permission. Click on “Grant admin consent for…”

Click on “Yes” to confirm.

As a last step, you need to generate a secret key. Select “Certificates & secret” and then “new client secret”.

Specify a description and choose the expiration time.

Once the secret has been generated, it is very important you take note of the secret value. You won’t able to retrieve it after you leave the page.

Conclusion

What you need to successfully call an API from a Logic App are:

Application (client) Id
Directory (tenant) Id
Client secret key

Logic App Development

Once the Azure Active Directory setup is complete, you can use the HTTP connector to make calls to the Graph API.

My requirement was to clone a team, so I had to first look for the source MS Teams id and then proceed with cloning operation.

The following picture shows the search operation.

This API return a json inside which you can find the MS Teams id, then you can call the cloning operation.

The URI is the follow:

@concat(‘https://graph.microsoft.com/v1.0/teams/’, body(‘Get_Teams_Template_Id’), ‘/clone’)

Additional notes

Keep in mind that some of the Graph API operations are not synchronous but may have a delay, especially when working with application permissions, you may won’t see the right permission in Teams until the permission synchronization has occurred (https://docs.microsoft.com/en-us/graph/api/group-post-owners?view=graph-rest-1.0).

Upload Flat File on Azure SQL Database using Azure Logic App

Posted on November 14, 2016 by Salvatore Pellitteri

In this article, I’m going to describe how to upload on-premises flat files on Azure SQL Database using Azure Logic Apps.

Logic Apps provide integration and workflows capabilities in the cloud. It provides a visual designer to model and automated processes as a series of steps.

In this sample, I also use Azure Service Bus as an entry point to the cloud because the file size is less than 1024 KB. Consider to use Azure Storage if you plan to upload file greater than that size.

Initial Consideration

Before you start, consider installing Microsoft Azure Logic Apps Enterprise Integration Tools for Visual Studio 2015 (https://www.microsoft.com/en-us/download/details.aspx?id=53016) in order to facilitate the creation of schemas and maps.

In addition, you can install Azure Logic Apps Tools for Visual Studio (https://visualstudiogallery.msdn.microsoft.com/e25ad307-46cf-412e-8ba5-5b555d53d2d9) to develop Logic App integration using visual studio.

At this link (https://azure.microsoft.com/en-us/tools/), you can find several tools for the development of solutions based on Microsoft Azure.

The following picture shows the data flow that I am going to describe.

Following the check list

Create Resource Group
Create Service Bus Namespace
Create Service Bus Queue
Create SQL Server
Create SQL Database
Create SQL Objects (Table, Schema and Procedures)
Configure API Connection to Service Bus
Configure API Connection to SQL Database
Create an Integration Account
Upload Schemas and Maps on Integration Account
Configure Transform Function (this step should be unnecessary in the next future)
Create and Test Logic App integration flow

To complete each step, you must first access the Microsoft Azure portal (https://portal.azure.com).

I think it is unnecessary to describe each step in detail, so I will just provide some screen shots.

Create Resource Group

Create Service Bus Namespace

Create Service Bus Queue

Create SQL Server (Logical Server)

Connect to SQL Server and create database objects

Before you can connect to SQL Database virtual server, you have to configure Azure firewall to allow the communication.

Use SQL Server Management Studio to create database and tables

Create database

/*
For additional information refer to address https://msdn.microsoft.com/en-us/library/dn268335.aspx
*/
CREATE DATABASE [int-fftosql-sqldb]
(
    EDITION = 'basic',
    SERVICE_OBJECTIVE='basic'
);

GO

Create table and procedures

CREATE XML SCHEMA COLLECTION [dbo].[OrderXml] AS
N'<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <xsd:element name="Root">
    <xsd:complexType mixed="true">
      <xsd:complexContent mixed="true">
        <xsd:restriction base="xsd:anyType">
          <xsd:sequence>
            <xsd:element name="Order" maxOccurs="unbounded">
              <xsd:complexType mixed="true">
                <xsd:complexContent mixed="true">
                  <xsd:restriction base="xsd:anyType">
                    <xsd:sequence>
                      <xsd:element name="Lines" type="OrderDetailType" maxOccurs="unbounded" />
                    </xsd:sequence>
                    <xsd:attribute name="orderDate" type="xsd:string" use="required" />
                    <xsd:attribute name="orderNumber" type="xsd:string" use="required" />
                    <xsd:attribute name="channel" type="xsd:string" use="required" />
                  </xsd:restriction>
                </xsd:complexContent>
              </xsd:complexType>
            </xsd:element>
          </xsd:sequence>
        </xsd:restriction>
      </xsd:complexContent>
    </xsd:complexType>
  </xsd:element>
  <xsd:complexType name="OrderDetailType" mixed="true">
    <xsd:complexContent mixed="true">
      <xsd:restriction base="xsd:anyType">
        <xsd:choice maxOccurs="unbounded">
          <xsd:element name="productName" type="xsd:string" />
          <xsd:element name="quantity" type="xsd:string" />
          <xsd:element name="value" type="xsd:string" />
        </xsd:choice>
      </xsd:restriction>
    </xsd:complexContent>
  </xsd:complexType>
</xsd:schema>'
GO

CREATE TABLE [dbo].[OrderHeader]
(
    [orderId] [uniqueidentifier] NOT NULL,
    [orderNumber] [varchar](20) NOT NULL,
    [orderDate] [datetime] NOT NULL,
    [channel] [varchar](50) NULL,
    CONSTRAINT [PK_OrderHeader] PRIMARY KEY CLUSTERED
    (
        [orderId] ASC
    )
)
GO

CREATE TABLE [dbo].[OrderLine]
(
    [orderLineId] [int] IDENTITY(1,1) NOT NULL,
    [orderId] [uniqueidentifier] NOT NULL,
    [productName] [varchar](100) NOT NULL,
    [quantity] [int] NOT NULL,
    [value] [numeric](18, 3) NOT NULL,
    CONSTRAINT [PK_OrderLine] PRIMARY KEY CLUSTERED
    (
        [orderLineId] ASC
    )
)
GO

/*
TEST STATEMENT:
-------------------------------
EXECUTE OrderInsert N'<Root>
  <Order orderDate="20160101" orderNumber="000001" channel="Internet">
    <Lines>
      <productName>Portable Radio</productName>
      <quantity>10</quantity>
      <value>23.5</value>
    </Lines>
    <Lines>
      <productName>6 red wine bottles</productName>
      <quantity>20</quantity>
      <value>30.9</value>
    </Lines>  
  </Order>
</Root>'
-------------------------------
*/
CREATE PROCEDURE [dbo].[OrderInsert]
    @orderStream VARCHAR(MAX)
AS
BEGIN

    -- Fix Incoming XML: Logic App add a "?" char at the beginning of the string
    SET @orderStream = REPLACE(@orderStream, '?<', '<');

    DECLARE @orderXml XML(OrderXml) = CAST(@orderStream AS XML(OrderXml));
    DECLARE @orderId UNIQUEIDENTIFIER = NEWID();

    INSERT INTO OrderHeader
    (
        orderId,
        orderNumber,
        orderDate,
        channel
    )
    SELECT
        @orderId,
        orderNumber = OrderObject.Header.value('./@orderNumber', 'varchar(20)'),
        orderDate = OrderObject.Header.value('./@orderDate', 'datetime'),
        channel = OrderObject.Header.value('./@channel', 'varchar(50)')
    FROM
        @orderXml.nodes('/Root/Order') AS OrderObject(Header);

    INSERT INTO OrderLine
    (
        orderId,
        productName,
        quantity,
        value
    )
    SELECT
        @orderId,
        productName = OrderObject.Lines.value('./productName[1]', 'varchar(100)'),
        quantity = OrderObject.Lines.value('./quantity[1]', 'int'),
        value = OrderObject.Lines.value('./value[1]', 'numeric(18,3)')
    FROM
        @orderXml.nodes('/Root/Order/Lines') AS OrderObject(Lines);

END
GO

You should see something like that.

Create API Connection to Service Bus queue

Before to configure the connection, you need to know the service bus connection string.

Create API Connection to SQL Server

Create Integration Account

Integration accounts allow you to manage artifacts such as schemas, maps, certificates, partners and agreements in order to build Integration Application cloud based apps.

For additional information, refer to https://azure.microsoft.com/en-us/documentation/articles/app-service-logic-enterprise-integration-create-integration-account/.

Configure Integration Account

Add source schema (PurchaseOrderFF.xsd)

<?xml version="1.0" encoding="utf-16"?>
<xs:schema xmlns="http://int1demoa1project.Order" xmlns:b="http://schemas.microsoft.com/BizTalk/2003" targetNamespace="http://int1demoa1project.Order" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:annotation>
    <xs:appinfo>
      <schemaEditorExtension:schemaInfo namespaceAlias="b" extensionClass="Microsoft.Azure.Integration.DesignTools.FlatFileExtension.FlatFileExtension" standardName="Flat File" xmlns:schemaEditorExtension="http://schemas.microsoft.com/BizTalk/2003/SchemaEditorExtensions" />
      <b:schemaInfo standard="Flat File" codepage="1250" xmlns:b="http://schemas.microsoft.com/BizTalk/2003" root_reference="Order" />
    </xs:appinfo>
  </xs:annotation>
  <xs:element name="Order">
    <xs:annotation>
      <xs:appinfo>
        <b:recordInfo structure="delimited" child_delimiter_type="hex" child_delimiter="0xD 0xA" child_order="postfix" />
      </xs:appinfo>
    </xs:annotation>
    <xs:complexType>
      <xs:sequence>
        <xs:element name="Header">
          <xs:annotation>
            <xs:appinfo>
              <b:recordInfo tag_name="H0" structure="delimited" child_delimiter_type="char" child_delimiter=";" child_order="prefix" />
            </xs:appinfo>
          </xs:annotation>
          <xs:complexType>
            <xs:sequence>
              <xs:element name="orderDate" type="xs:string">
                <xs:annotation>
                  <xs:appinfo>
                    <b:fieldInfo justification="left" />
                  </xs:appinfo>
                </xs:annotation>
              </xs:element>
              <xs:element name="orderNumber" type="xs:string">
                <xs:annotation>
                  <xs:appinfo>
                    <b:fieldInfo justification="left" />
                  </xs:appinfo>
                </xs:annotation>
              </xs:element>
              <xs:element name="channel" type="xs:string">
                <xs:annotation>
                  <xs:appinfo>
                    <b:fieldInfo justification="left" />
                  </xs:appinfo>
                </xs:annotation>
              </xs:element>
            </xs:sequence>
          </xs:complexType>
        </xs:element>
        <xs:element maxOccurs="unbounded" name="Detail">
          <xs:annotation>
            <xs:appinfo>
              <b:recordInfo tag_name="D0" structure="delimited" child_delimiter_type="char" child_delimiter=";" child_order="prefix" />
            </xs:appinfo>
          </xs:annotation>
          <xs:complexType>
            <xs:sequence>
              <xs:element name="productName" type="xs:string">
                <xs:annotation>
                  <xs:appinfo>
                    <b:fieldInfo justification="left" />
                  </xs:appinfo>
                </xs:annotation>
              </xs:element>
              <xs:element name="quantity" type="xs:string">
                <xs:annotation>
                  <xs:appinfo>
                    <b:fieldInfo justification="left" />
                  </xs:appinfo>
                </xs:annotation>
              </xs:element>
              <xs:element name="value" type="xs:string">
                <xs:annotation>
                  <xs:appinfo>
                    <b:fieldInfo justification="left" />
                  </xs:appinfo>
                </xs:annotation>
              </xs:element>
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>

Add SQL Server stored procedure schema (PurchaseOrderDB.xsd)

<?xml version="1.0" encoding="utf-16"?>
<xsd:schema xmlns:b="http://schemas.microsoft.com/BizTalk/2003" attributeFormDefault="unqualified" elementFormDefault="unqualified" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <xsd:complexType name="OrderDetailType" mixed="true">
    <xsd:choice minOccurs="1" maxOccurs="unbounded">
      <xsd:element minOccurs="1" maxOccurs="1" name="productName" type="xsd:string" />
      <xsd:element minOccurs="1" maxOccurs="1" name="quantity" type="xsd:string" />
      <xsd:element minOccurs="1" maxOccurs="1" name="value" type="xsd:string" />
    </xsd:choice>
  </xsd:complexType>
  <xsd:element name="Root">
    <xsd:complexType mixed="true">
      <xsd:sequence>
        <xsd:element minOccurs="1" maxOccurs="unbounded" name="Order">
          <xsd:complexType mixed="true">
            <xsd:sequence>
              <xsd:element minOccurs="1" maxOccurs="unbounded" name="Lines" type="OrderDetailType" />
            </xsd:sequence>
            <xsd:attribute name="orderDate" type="xsd:string" use="required" />
            <xsd:attribute name="orderNumber" type="xsd:string" use="required" />
            <xsd:attribute name="channel" type="xsd:string" use="required" />
          </xsd:complexType>
        </xsd:element>
      </xsd:sequence>
    </xsd:complexType>
  </xsd:element>
</xsd:schema>

Add XML Transformation Map

<?xml version="1.0" encoding="utf-16"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt" xmlns:var="http://schemas.microsoft.com/BizTalk/2003/var" exclude-result-prefixes="msxsl var s0" version="1.0" xmlns:s0="http://int1demoa1project.Order">
  <xsl:output omit-xml-declaration="yes" encoding="windows-1250" version="1.0" indent="no" method="xml" />
  <xsl:template match="/">
    <xsl:apply-templates select="/s0:Order" />
  </xsl:template>
  <xsl:template match="/s0:Order">
    <Root>
      <Order>
        <xsl:attribute name="orderDate">
          <xsl:value-of select="Header/orderDate/text()" />
        </xsl:attribute>
        <xsl:attribute name="orderNumber">
          <xsl:value-of select="Header/orderNumber/text()" />
        </xsl:attribute>
        <xsl:attribute name="channel">
          <xsl:value-of select="Header/channel/text()" />
        </xsl:attribute>
        <xsl:for-each select="Detail">
          <Lines>
            <productName>
              <xsl:value-of select="productName/text()" />
            </productName>
            <quantity>
              <xsl:value-of select="quantity/text()" />
            </quantity>
            <value>
              <xsl:value-of select="value/text()" />
            </value>
            <xsl:value-of select="./text()" />
          </Lines>
        </xsl:for-each>
      </Order>
    </Root>
  </xsl:template>
</xsl:stylesheet>

Temporary operations

Create Storage Account

Create App Service Plan

Create Function App

Add the transformation function

To add the transformation Function for Logic App run the following link

https://portal.azure.com/#create/Microsoft.Template/uri/https%3A%2F%2Fraw.githubusercontent.com%2FAzure%2Fazure-quickstart-templates%2Fmaster%2F201-logic-app-transform-function%2Fazuredeploy.json

Create Logic App

Associate Integration Account to the Logic App

Create a blank solution

Configure trigger

Modify the polling frequency if you need to process the file in near real time.

Add Flat-File decoding to parse flat file and fill into XSD schema message.

Add the map to transform flat file message to SQL Database XML schema type.

Configure the call to the stored procedure.

Write and run client application

Now you able to run the client application. To create a sample application, follow the steps below.

1 – Install WindowsAzure.ServiceBus NuGet Package

2 – Get service bus Connection String

3 – Write your code


using Microsoft.ServiceBus.Messaging;

...
using (FileStream file = File.Open(<FilePath>, FileMode.Open, FileAccess.Read, FileShare.Delete))
{

  // Send
  file.Seek(0, SeekOrigin.Begin);

  var queueClient = QueueClient.CreateFromConnectionString(<ConnectionString>, <QueueName>);
  BrokeredMessage serviceBusMessage;

  serviceBusMessage = new BrokeredMessage(file);

  queueClient.Send(serviceBusMessage);

}

Integrate Azure SQL Database and OneDrive with Logic Apps

Posted on November 2, 2015 by Salvatore Pellitteri

This sample shows how to read information from an Azure SQL Database and send them to OneDrive folder using a simple Logic Apps.

I will use a polling statement in order to intercept new data available on SQL Database, transform to a new information schema and then write a XML formatted file in an OneDrive folder.

Following I will demonstrate how to setup the sample environment, how to configure the connection to Azure SQL Database and then how to create the integration flow using Azure Logic Apps.

Setup Environment

First of all, you need to create a resource group that will contains the SQL Database instance and all the other items needed for Logic Apps.

To create a resource group, navigate the new azure portal, click on Resource groups and click on Add.

Specify a Resource group name and then click on Create.

Now you are able to create a SQL Database instance. To do that select SQL Databases from the portal and select Add.

You can choose to add the database to an existing server or create a new server. In this demonstration, I have created a new server.

Once you have specified the server name, the user name and password you will use to connect to the database, the resource group previously created and the database name click on the create button to complete the operation.

Now you need to open the Azure firewall in order to connect to the database using Microsoft SQL Server Management Studio. So click on server name link.

Then click on Show firewall settings link.

The interface will show you the current IP address of your machine. So click on Ass client IP.

Save the setting.

After a few seconds, the firewall will be updated.

Now you are ready to connect to the SQL database instance. Open SQL Server Management Studio console and connect to the database using the SQL Server Authentication. Provide the credential you have chosen in the previous step.

Now you are connected to the Azure database.

Now, we have to create the table that contains the information that the Logic Apps will intercepts and will sent to OneDrive.

Click on new query and run the following statement.

— Source Table

CREATE
TABLE [dbo].[SourceTable]

(

[sourceTableId] [int] NOT
NULL,

[transactionStatus] [varchar](10)
NOT
NULL,

[transactionId] [uniqueidentifier] NULL,

[sampleContent] [varchar](100)
NOT
NULL,

CONSTRAINT [PK_SourceTable] PRIMARY
KEY
CLUSTERED

(

[sourceTableId] ASC

)

— Available Statement

CREATE
PROCEDURE [dbo].[AvailableStmt]

BEGIN

SELECT

COUNT(*)

FROM

SourceTable

WHERE

transactionStatus =
‘To Process’;

END

— Polling Statement

CREATE
PROCEDURE [dbo].[PollingStmt]

BEGIN

DECLARE @transactionId UNIQUEIDENTIFIER
=
NEWID();

UPDATE SourceTable

SET

transactionStatus =
‘Processed’,

transactionId = @transactionId

WHERE

transactionStatus =
‘To Process’;

SELECT

sourceTableId,

sampleContent

FROM

SourceTable WITH (NOLOCK)

WHERE

transactionId = @transactionId;

END

Create the Microsoft SQL Connector

Once you have created all you need on SQL Server, you can define the database connection. From the portal home page click on Marketplace.

Find for Microsoft SQL Connector.

Select the connector and click on Create.

Specify a Name, a Logic App Plan (or create a new one) a resource group and then click on Create.

Now you have to specify the server name, the user name and password, the database name.

Scrolling down you have to specify the most important things: The Available Statement and the Polling Statement. The first one is the statement that checks for new information on the database. The second one, gets the information and make them available for the process.

Create the Artifact

In this demonstration, we will transform the information coming from the SQL database to a different information scheme. To do this we will need a map.

You can define a map using Visual Studio and Microsoft Azure BizTalk Services SDK available at http://www.microsoft.com/en-us/download/details.aspx?id=39087.

First, we need to download the schema that will act as a source schema.

To do that, navigate the portal in the all resources list and select the connection to SQL Server previously created. Then select the Download Schema link.

After installed Microsoft Azure BizTalk SDK, open Visual Studio and create a new project of type BizTalk Service Artifact.

Delete al unneeded objects created by the project template.

Then import the schema downloaded in the previous step.

Now, create a new schema that will acts as the target schema. Following just a simple schema.

Finally you can create a new map to transform the source schema.

Define the mapping rules.

Close and save the project.

Now we will define a transformation service on Azure that will contains the map.

From the Azure Marketplace find for BizTalk Transform Service.

Select and specify a name, an App Service Plan (this must be the same used by the SQL Connector) and click on Create.

Navigate the Transform Service and select Maps.

In the right panel will compare a new form with a list of maps. Clock on Add.

Select the “trfm” file created by Visual Studio Solution.

Create the Logic App

Now you are ready to create the Logic App.

Navigate the Azure Portal, select Logic Apps and click on Add.

Specify Logic App name, the resources group and click on create.

Once the Logic App is created click on Edit.

There are many Logic App templates. In this demonstration I choose a blank Logic App selecting “Create from Scratch”.

On the right panel select the “Microsoft SQL Connector” created previously.

Specify “Poll Data (XML)”.

Specify the polling interval. Depending on the Pricing Tier you can specify different measures as polling interval.

Confirm your selection and select “BizTalk Transform Service”.

Click on Arrow to start the configuration.

Then click on Tranform.

Choose the output of Microsoft SQL Connector as map input transform.

Then specify (manually) the map name.

Finally select the OneDrive connector.

You have to autorize the access to your OneDrive subscription.

Then I chosen “Upload a file”.

Specify a File Path and the Transform output as file content.

In this case no content transfer encoding is needed so specify “none”.

Once the configuration is completed, you should see the following integration flow.

Test the sample

To test the sample is enough to insert a row into SQL database table. You can use the following statement to do that.

insert
into SourceTable

(

sourceTableId,

transactionStatus,

sampleContent

)

values

(

‘To Process’,

‘Content to transfer’

);

After a few seconds, you should see the file into your OneDrive subscription.

In the following picture, you can see the content of the result file.