About Salvatore Pellitteri

I live in Italy. I'm Microsoft Integration Architect and Developer Team Manager at Microsys. I deal with application systems integration projects using BizTalk Server and business intelligence projects using the SQL Server platform. I have been working on Microsoft's cloud technologies such as Azure Logic Apps, Azure Data Factory, Service Bus and other related technologies for several years. I work in many big integration project with several Italian enterprise organizations integrating many application systems like SAP, Oracle, SQL Server, Salesforce, Microsoft Dynamics, AS400, J.D. Edwards, EDI and HL7. I have been Microsoft MVP Azure for 3 years.

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!

BizTalk Server 2020 TMS: String was not recognized as a valid DateTime

Posted on May 5, 2022 by Salvatore Pellitteri

Last week a colleague of mine, configuring the BizTalk TMS service, reported me the error “String was not recognized as a valid DateTime”.

Starting the service, we found the following two messages in the Windows Server event log:

Figure 1 – String was not recognized as a valid DateTime

Figure 2 – The given key was not present in the dictionary

We tried to uninstall the service, reinstall it and reapply the BizTalk Server 2020 CU3 but nothing. Until then, we tried to change the regional settings and everything started working.

It’s not about the fact that they didn’t like a specific regional setting because putting the original one back kept working. Rather it appears that there was a condition that caused the exception and changing the settings resolved.

I wanted to share this little experience because we have not found anything about it.

New feature in the BizTalk Password Manager utility

Posted on June 10, 2019 by Salvatore Pellitteri

A couple of years ago I had released a small command line tool to set the passwords of the send ports (primary transport only) and the receive locations.

You can find the latest release of this tool at https://github.com/pellitteris/BizTalkPassword.

This allowed me to create scripts that, in the case of a complete application deployment, set all the necessary passwords reducing manual operations that often can cause errors.

This is just an example that can be extended if necessary. However, it has several features that can be used as is.

In the release I released a few days ago I implemented some new features with the aim of facilitating and therefore speeding up the updating of the scripts.

In particular I added these features:

List all the port that have a user account and a password set
Generate script to set passwords

To list all the port that have a user account and a password set, is enough type the following command:

Here the result:

The output is made in such a way that it can be easily imported into an Excel spreadsheet for documentation purposes.

To set passwords it is necessary to execute the “set” command. Being a command line tool, it is possible to create a batch file that sets all the necessary usernames/passwords. Keeping the script up to date can be a problem or a rather expensive activity, so I added a couple of features to simplify the already difficult deployment adventure.

In this regard it is possible to execute the “generatescript” command which generates a file with the necessary commands. The command can be executed as is, and in this case the passwords will have a placeholder “######” or can be executed with a json mapping file.

To generate the mapping file, run the following command:

The command generates a file .json like the following:

Now you have to substitute “null” with the password, then you can run the following command:

As a result you will have a “cmd” file with the commands ready to be executed.

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).

Configuring BizTalk 2016 in High Availability Config with SQL Server 2016 Always On

Posted on May 30, 2018 by Salvatore Pellitteri

This article tells about our recent experience with an installation of BizTalk Server 2016 in high availability configuration with SQL Server 2016 Always On. It’s just a small experience, but I hope it can be useful for anyone who wants to install a BizTalk infrastructure in high availability on Microsoft Azure or in a virtual environment where cluster failover is not well accepted by system engineers.

There are several articles dealing with this topic and we highly recommend reading them carefully.

In addition, we suggest you to read the article related to Master Secret Server configuration in cluster (https://docs.microsoft.com/en-us/biztalk/core/how-to-cluster-the-master-secret-server1).

To design our server infrastructure, we referred to the scenario suggested by the Microsoft article.

Anyway, our implementation is smaller than the examples you can see in the article. This because it often happens to have the need for configurations in high availability but not to have a very high load.

As recommend, we have grouped BizTalk Server databases into the following four SQL Server instances:

The subdivision of the instances is due to the fact that MSDTC on SQL Server 2016 Always-On Availability Group is not able to handle distributed transactions between multiple databases within the same instance. We are all waiting for BizTalk to support the 2017 version of SQL that is able to do that.

In this specific case we don’t need ESB toolkit databases and BAM Analysis and Alert, so our final cluster implementation is illustrated in the figure below.

As you can see, we have configured one Listener for each SQL Server instance.

We do not report the installation and configuration of SQL Server as it is extensively described in the articles we referred to.

SQL Server Installed Features

On each server node and node instance we have installed the following services and features:

Database Engine Services
- SQL Server Replication
- Full-Text and Semantic Extractions for Search
Shared Features
- Client Tools Connectivity
- Integration Services

DTC Configuration

In this configuration it is not necessary to configure a DTC instance as a cluster resource because a local DTC instance will be used.

To enable Network DTC Access on the BizTalk Server and the SQL Server follow the step below:

In the Start menu, open “dcomcnfg”.
- Or, open Administrative Tools, and then select Component Services.
- Or, open Server Manager, select Tools, and then select Component Services.
Expand Component Services, expand Computers, expand My Computer, and expand Distributed Transaction Coordinator.
Right-click Local DTC, and select Properties.
Go to the Security tab, and check the following:
- Network DTC Access
- Allow Inbound
- Allow Outbound
- No Authentication Required
Select OK. If prompted to restart MS DTC, select Yes.

Enable remote access to COM

Follow the instructions in the following article to enable remote access to COM.

https://support.microsoft.com/en-us/help/3182294/0x80004027-error-when-you-try-to-remotely-access-com-object-after-you

Cluster resource check

From the failover cluster management console, enable the prevent back option in each resource group created.

This may be your preferred setting because it allows you to control when the failback occurs. For example, you may want to select Prevent failback if you want to take time to troubleshoot or run diagnostics on the failed node before allowing the node to take ownership of the SQL Server again.

Availability Group Options

Make sure Availability Groups are configured with the Per Database DTC Support option.

Also configure “Readable Secondary” equal to “Yes”. Otherwise the SSO service could generate the following error:

Failed to contact the SSO database: The target database, ‘SSODB’, is participating in an availability group and is currently not accessible for queries. Either data movement is suspended or the availability replica is not enabled for read access. To allow read-only access to this and other databases in the availability group, enable read access to one or more secondary availability replicas in the group. For more information, see the ALTER AVAILABILITY GROUP statement in SQL Server Books Online.
Data Source=***;Integrated Security=SSPI;Initial Catalog=SSODB

Error code: 0x800710D9, Unable to read from or write to the database.

Finally, you must check that “Primary” has been selected in the Backup Preferences tab, in the “Where backup should occur” configuration setting.

SSO Setup

After completing the installation of SQL Server, before proceeding with the installation of BizTalk, you need to install the Enterprise Single Sign-On master secret server.

We will follow this article regarding the installation of the clustered SSO service: https://docs.microsoft.com/en-us/biztalk/core/how-to-cluster-the-master-secret-server1.

We will set up a cluster group that will contain IP address, Network Name and a generic service resource for the SSO service.

Execute the following step on both SQL Server nodes.

Enable only the following components

Enterprise Single Sign-On Administration Module
Enterprise Single Sign-On Master Secret Server

Execute the following step on first SQL Server node

Execute the following step on second node

To configure SSO in failover cluster follow the below steps:

Restart the Enterprise SSO service on the first node
From the command prompt type the following command:

“<drive>:\Program Files\Common Files\Enterprise Single Sign-On\ssomanage” -updatedb XMLFile

Where XMLFile is a file with the following content “<sso><globalInfo><secretServer>[SSO NET NAME CLUSTER RES]</secretServer></globalInfo></sso>”.
Configure manual start type the “Enterprise Single Sign-On Service” service on both servers.
From the failover cluster management console create a Generic Services resource for the SSO service
Restore the master secret on the second cluster node

Configure SSO Database replica

Before to start to configure SSODB database replica you have to execute e full backup of database.

Install BizTalk Server Application Server

Before starting with the BizTalk Server installation, the prerequisites must be installed. Follow the step described in the Microsoft article https://docs.microsoft.com/en-us/biztalk/install-and-config-guides/set-up-and-install-prerequisites-for-biztalk-server-2016.

The article shows a series of mandatory and optional steps. Following what I have done:

Enable Network DTC Access
Disable Windows Firewall
Disable UAC
Enable IIS
Run 64-bit BAM portal
Install Visual C++ redistributable package
Install SQL XML 4
SQL Server Integration Services

BizTalk Setup

Execute the following step on both BizTalk Servers.

Enable all available components

BizTalk Configuration (First Node)

In general, the configuration of the first BizTalk node creates configuration databases while the configuration of the second node is limited to joining the existing farm.

BizTalk Configuration (Second Node)

Final Database Configuration

Database Replica

Now that all the databases have been created it is necessary to proceed with the configuration of the replica within the different SQL Server availability groups. Below are the steps to configure the BizTalkMgmtDb database.

Repeat these steps for each database created by the configuration procedure.

Linked Server & Job

Linked servers and jobs are automatically created by the configuration procedure, anyway it is better to verify that they have been created on both nodes.

It is also necessary to modify all the steps of each job to include the control that the server running is the primary one. So, include the original step inside the following code:

if (sys.fn_hadr_is_primary_replica(‘<DBNAME>’)
= 1)
begin

[original step commands]

end;

I suggest to use the validation script for BizTalk Availability Group (https://skastberg.wordpress.com/2017/06/30/validate-your-biztalk-availability-groups/) in order to check the configuration, it is a very useful tool.

I want to thank my colleague Paolo Barbieri who helped me to reason during this little adventure and write this article.

BizTalk tools available on GitHub

Posted on September 1, 2017 by Salvatore Pellitteri

In some of the articles I wrote, I published part of the tools I and my team use in the integration projects developed with BizTalk Server.

These tools are available on CodePlex, but as many of you know, next December 15, CodePlex will be shut down, so we decided to make our work available on GitHub.

BizTalk Deployment Utility (BizTalkDiff)

BizTalk Deployment Utility is a simple tool allows you to compare two systems based on BizTalk Server and align environments by copying specific ports from a source system to a target system.

With this application you can check the existance of applications, receive ports, receive locations, send ports and send port groups, and decide to create specific objects on the target system. It is a kind of BizTalk Diff.

You can now find this tool at https://github.com/pellitteris/BizTalkDiff.

BizTalk Password Manager

This little tool allows you to set BizTalk send ports and receive locations passwords. You can use it to create automated scripts during deployment operations.

It is just an example that currently manages the adapter FILE, FTP, SFTP, POP3 and WCF *. Of course you can access the source code in order to manage other kind of adapters.

You can now find this tool at https://github.com/pellitteris/BizTalkPassword.

BizTalk Configuration Reports

BizTalk Configuration Reporting allows you to view and document the current configuration of BizTalk Server in terms of receive ports, send ports and orchestrations.

This tool consists of a database to be placed in the same database instance of BizTalkMgmtDb and a some reports to be installed in an instance of Microsoft SQL Server Reporting Services (SSRS).

The reports, being based on SSRS, display data in real time and allow export in different formats such as Microsoft Word, PDF, Microsoft Excel, TIFF, etc.

This allow to have documentation always up to date.

You can now find this tool at https://github.com/pellitteris/BizTalkConfigReports.

BizTalk Deployment Utility (BizTalk Diff) is growing

Posted on February 23, 2017 by Salvatore Pellitteri

Thanks to my team that helps me face increasingly difficult missions in my work, the BizTalk Diff grows by inserting new features. In particular, I want to thank Matteo Chieli who did much of the work.

You can find the updated version of the tool at https://github.com/pellitteris/BizTalkDiff/ address.

In Particular, it is now possible to apply some changes in block to the configuration:

You can set the following settings:

Change send port host and transport options (Retry Count, Retry Interval and Enable Routing for failed messages)

Change receive location host

Change orchestration host

Change WCF based send port timeouts

Change WCF based receive location timeouts

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);

}

Upload Dataset on Azure SQL Database using Azure Event Hub and Azure Stream Analytics

Posted on November 8, 2016 by Salvatore Pellitteri

There are several ways to upload data to SQL Database, including SSIS, BCP, API and several other ways.

For those who work with SQL Server it is natural to use tools that historically belong to the SQL platform, so I wanted to write this article to expose an alternative and to talk about different techniques that apply to different scenarios.

When designing an architecture, you may find yourselves at a crossroads, and often you do not know what to expect once made a decision. How requirements will change over time.

Azure Event hub allows us to decouple who collects the data (the client) from who receives the data, allow you to change kind of receivers or add additional targets.

For this reason, I consider it the entry point to Microsoft Azure.

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