Upload Flat File on Azure SQL Database using Azure Logic App

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

}

Salvatore Pellitteri Biztalk Blog

Upload Flat File on Azure SQL Database using Azure Logic App

Initial Consideration

Create Resource Group

Create Service Bus Namespace

Create Service Bus Queue

Create SQL Server (Logical Server)

Connect to SQL Server and create database objects

Use SQL Server Management Studio to create database and tables

Create database

Create table and procedures

Create API Connection to Service Bus queue

Create API Connection to SQL Server

Create Integration Account

Configure Integration Account

Add source schema (PurchaseOrderFF.xsd)

Add SQL Server stored procedure schema (PurchaseOrderDB.xsd)

Add XML Transformation Map

Temporary operations

Create Storage Account

Create App Service Plan

Create Function App

Add the transformation function

Create Logic App

Associate Integration Account to the Logic App

Create a blank solution

Configure trigger

Write and run client application

Leave a Reply Cancel reply

Initial Consideration

Create Resource Group

Create Service Bus Namespace

Create Service Bus Queue

Create SQL Server (Logical Server)

Connect to SQL Server and create database objects

Use SQL Server Management Studio to create database and tables

Create database

Create table and procedures

Create API Connection to Service Bus queue

Create API Connection to SQL Server

Create Integration Account

Configure Integration Account

Add source schema (PurchaseOrderFF.xsd)

Add SQL Server stored procedure schema (PurchaseOrderDB.xsd)

Add XML Transformation Map

Temporary operations

Create Storage Account

Create App Service Plan

Create Function App

Add the transformation function

Create Logic App

Associate Integration Account to the Logic App

Create a blank solution

Configure trigger

Write and run client application

Share this:

Like this:

Related

Leave a Reply Cancel reply