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
1: /*
2: For additional information refer to address https://msdn.microsoft.com/en-us/library/dn268335.aspx
3: */
4: CREATE DATABASE [int-fftosql-sqldb]
5: (
6: EDITION = 'basic',
7: SERVICE_OBJECTIVE='basic'
8: );
9:
10: GO
Create table and procedures
1: CREATE XML SCHEMA COLLECTION [dbo].[OrderXml] AS
2: N'<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
3: <xsd:element name="Root">
4: <xsd:complexType mixed="true">
5: <xsd:complexContent mixed="true">
6: <xsd:restriction base="xsd:anyType">
7: <xsd:sequence>
8: <xsd:element name="Order" maxOccurs="unbounded">
9: <xsd:complexType mixed="true">
10: <xsd:complexContent mixed="true">
11: <xsd:restriction base="xsd:anyType">
12: <xsd:sequence>
13: <xsd:element name="Lines" type="OrderDetailType" maxOccurs="unbounded" />
14: </xsd:sequence>
15: <xsd:attribute name="orderDate" type="xsd:string" use="required" />
16: <xsd:attribute name="orderNumber" type="xsd:string" use="required" />
17: <xsd:attribute name="channel" type="xsd:string" use="required" />
18: </xsd:restriction>
19: </xsd:complexContent>
20: </xsd:complexType>
21: </xsd:element>
22: </xsd:sequence>
23: </xsd:restriction>
24: </xsd:complexContent>
25: </xsd:complexType>
26: </xsd:element>
27: <xsd:complexType name="OrderDetailType" mixed="true">
28: <xsd:complexContent mixed="true">
29: <xsd:restriction base="xsd:anyType">
30: <xsd:choice maxOccurs="unbounded">
31: <xsd:element name="productName" type="xsd:string" />
32: <xsd:element name="quantity" type="xsd:string" />
33: <xsd:element name="value" type="xsd:string" />
34: </xsd:choice>
35: </xsd:restriction>
36: </xsd:complexContent>
37: </xsd:complexType>
38: </xsd:schema>'
39: GO
40:
41: CREATE TABLE [dbo].[OrderHeader]
42: (
43: [orderId] [uniqueidentifier] NOT NULL,
44: [orderNumber] [varchar](20) NOT NULL,
45: [orderDate] [datetime] NOT NULL,
46: [channel] [varchar](50) NULL,
47: CONSTRAINT [PK_OrderHeader] PRIMARY KEY CLUSTERED
48: (
49: [orderId] ASC
50: )
51: )
52: GO
53:
54: CREATE TABLE [dbo].[OrderLine]
55: (
56: [orderLineId] [int] IDENTITY(1,1) NOT NULL,
57: [orderId] [uniqueidentifier] NOT NULL,
58: [productName] [varchar](100) NOT NULL,
59: [quantity] [int] NOT NULL,
60: [value] [numeric](18, 3) NOT NULL,
61: CONSTRAINT [PK_OrderLine] PRIMARY KEY CLUSTERED
62: (
63: [orderLineId] ASC
64: )
65: )
66: GO
67:
68: /*
69: TEST STATEMENT:
70: -------------------------------
71: EXECUTE OrderInsert N'<Root>
72: <Order orderDate="20160101" orderNumber="000001" channel="Internet">
73: <Lines>
74: <productName>Portable Radio</productName>
75: <quantity>10</quantity>
76: <value>23.5</value>
77: </Lines>
78: <Lines>
79: <productName>6 red wine bottles</productName>
80: <quantity>20</quantity>
81: <value>30.9</value>
82: </Lines>
83: </Order>
84: </Root>'
85: -------------------------------
86: */
87: CREATE PROCEDURE [dbo].[OrderInsert]
88: @orderStream VARCHAR(MAX)
89: AS
90: BEGIN
91:
92: -- Fix Incoming XML: Logic App add a "?" char at the beginning of the string
93: SET @orderStream = REPLACE(@orderStream, '?<', '<');
94:
95: DECLARE @orderXml XML(OrderXml) = CAST(@orderStream AS XML(OrderXml));
96: DECLARE @orderId UNIQUEIDENTIFIER = NEWID();
97:
98: INSERT INTO OrderHeader
99: (
100: orderId,
101: orderNumber,
102: orderDate,
103: channel
104: )
105: SELECT
106: @orderId,
107: orderNumber = OrderObject.Header.value('./@orderNumber', 'varchar(20)'),
108: orderDate = OrderObject.Header.value('./@orderDate', 'datetime'),
109: channel = OrderObject.Header.value('./@channel', 'varchar(50)')
110: FROM
111: @orderXml.nodes('/Root/Order') AS OrderObject(Header);
112:
113: INSERT INTO OrderLine
114: (
115: orderId,
116: productName,
117: quantity,
118: value
119: )
120: SELECT
121: @orderId,
122: productName = OrderObject.Lines.value('./productName[1]', 'varchar(100)'),
123: quantity = OrderObject.Lines.value('./quantity[1]', 'int'),
124: value = OrderObject.Lines.value('./value[1]', 'numeric(18,3)')
125: FROM
126: @orderXml.nodes('/Root/Order/Lines') AS OrderObject(Lines);
127:
128: END
129: 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)
1: <?xml version="1.0" encoding="utf-16"?>
2: <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">
3: <xs:annotation>
4: <xs:appinfo>
5: <schemaEditorExtension:schemaInfo namespaceAlias="b" extensionClass="Microsoft.Azure.Integration.DesignTools.FlatFileExtension.FlatFileExtension" standardName="Flat File" xmlns:schemaEditorExtension="http://schemas.microsoft.com/BizTalk/2003/SchemaEditorExtensions" />
6: <b:schemaInfo standard="Flat File" codepage="1250" xmlns:b="http://schemas.microsoft.com/BizTalk/2003" root_reference="Order" />
7: </xs:appinfo>
8: </xs:annotation>
9: <xs:element name="Order">
10: <xs:annotation>
11: <xs:appinfo>
12: <b:recordInfo structure="delimited" child_delimiter_type="hex" child_delimiter="0xD 0xA" child_order="postfix" />
13: </xs:appinfo>
14: </xs:annotation>
15: <xs:complexType>
16: <xs:sequence>
17: <xs:element name="Header">
18: <xs:annotation>
19: <xs:appinfo>
20: <b:recordInfo tag_name="H0" structure="delimited" child_delimiter_type="char" child_delimiter=";" child_order="prefix" />
21: </xs:appinfo>
22: </xs:annotation>
23: <xs:complexType>
24: <xs:sequence>
25: <xs:element name="orderDate" type="xs:string">
26: <xs:annotation>
27: <xs:appinfo>
28: <b:fieldInfo justification="left" />
29: </xs:appinfo>
30: </xs:annotation>
31: </xs:element>
32: <xs:element name="orderNumber" type="xs:string">
33: <xs:annotation>
34: <xs:appinfo>
35: <b:fieldInfo justification="left" />
36: </xs:appinfo>
37: </xs:annotation>
38: </xs:element>
39: <xs:element name="channel" type="xs:string">
40: <xs:annotation>
41: <xs:appinfo>
42: <b:fieldInfo justification="left" />
43: </xs:appinfo>
44: </xs:annotation>
45: </xs:element>
46: </xs:sequence>
47: </xs:complexType>
48: </xs:element>
49: <xs:element maxOccurs="unbounded" name="Detail">
50: <xs:annotation>
51: <xs:appinfo>
52: <b:recordInfo tag_name="D0" structure="delimited" child_delimiter_type="char" child_delimiter=";" child_order="prefix" />
53: </xs:appinfo>
54: </xs:annotation>
55: <xs:complexType>
56: <xs:sequence>
57: <xs:element name="productName" type="xs:string">
58: <xs:annotation>
59: <xs:appinfo>
60: <b:fieldInfo justification="left" />
61: </xs:appinfo>
62: </xs:annotation>
63: </xs:element>
64: <xs:element name="quantity" type="xs:string">
65: <xs:annotation>
66: <xs:appinfo>
67: <b:fieldInfo justification="left" />
68: </xs:appinfo>
69: </xs:annotation>
70: </xs:element>
71: <xs:element name="value" type="xs:string">
72: <xs:annotation>
73: <xs:appinfo>
74: <b:fieldInfo justification="left" />
75: </xs:appinfo>
76: </xs:annotation>
77: </xs:element>
78: </xs:sequence>
79: </xs:complexType>
80: </xs:element>
81: </xs:sequence>
82: </xs:complexType>
83: </xs:element>
84: </xs:schema>
Add SQL Server stored procedure schema (PurchaseOrderDB.xsd)
1: <?xml version="1.0" encoding="utf-16"?>
2: <xsd:schema xmlns:b="http://schemas.microsoft.com/BizTalk/2003" attributeFormDefault="unqualified" elementFormDefault="unqualified" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
3: <xsd:complexType name="OrderDetailType" mixed="true">
4: <xsd:choice minOccurs="1" maxOccurs="unbounded">
5: <xsd:element minOccurs="1" maxOccurs="1" name="productName" type="xsd:string" />
6: <xsd:element minOccurs="1" maxOccurs="1" name="quantity" type="xsd:string" />
7: <xsd:element minOccurs="1" maxOccurs="1" name="value" type="xsd:string" />
8: </xsd:choice>
9: </xsd:complexType>
10: <xsd:element name="Root">
11: <xsd:complexType mixed="true">
12: <xsd:sequence>
13: <xsd:element minOccurs="1" maxOccurs="unbounded" name="Order">
14: <xsd:complexType mixed="true">
15: <xsd:sequence>
16: <xsd:element minOccurs="1" maxOccurs="unbounded" name="Lines" type="OrderDetailType" />
17: </xsd:sequence>
18: <xsd:attribute name="orderDate" type="xsd:string" use="required" />
19: <xsd:attribute name="orderNumber" type="xsd:string" use="required" />
20: <xsd:attribute name="channel" type="xsd:string" use="required" />
21: </xsd:complexType>
22: </xsd:element>
23: </xsd:sequence>
24: </xsd:complexType>
25: </xsd:element>
26: </xsd:schema>
Add XML Transformation Map
1: <?xml version="1.0" encoding="utf-16"?>
2: <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">
3: <xsl:output omit-xml-declaration="yes" encoding="windows-1250" version="1.0" indent="no" method="xml" />
4: <xsl:template match="/">
5: <xsl:apply-templates select="/s0:Order" />
6: </xsl:template>
7: <xsl:template match="/s0:Order">
8: <Root>
9: <Order>
10: <xsl:attribute name="orderDate">
11: <xsl:value-of select="Header/orderDate/text()" />
12: </xsl:attribute>
13: <xsl:attribute name="orderNumber">
14: <xsl:value-of select="Header/orderNumber/text()" />
15: </xsl:attribute>
16: <xsl:attribute name="channel">
17: <xsl:value-of select="Header/channel/text()" />
18: </xsl:attribute>
19: <xsl:for-each select="Detail">
20: <Lines>
21: <productName>
22: <xsl:value-of select="productName/text()" />
23: </productName>
24: <quantity>
25: <xsl:value-of select="quantity/text()" />
26: </quantity>
27: <value>
28: <xsl:value-of select="value/text()" />
29: </value>
30: <xsl:value-of select="./text()" />
31: </Lines>
32: </xsl:for-each>
33: </Order>
34: </Root>
35: </xsl:template>
36: </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
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
1:
2: using Microsoft.ServiceBus.Messaging;
3:
4: ...
5: using (FileStream file = File.Open(<FilePath>, FileMode.Open, FileAccess.Read, FileShare.Delete))
6: {
7:
8: // Send
9: file.Seek(0, SeekOrigin.Begin);
10:
11: var queueClient = QueueClient.CreateFromConnectionString(<ConnectionString>, <QueueName>);
12: BrokeredMessage serviceBusMessage;
13:
14: serviceBusMessage = new BrokeredMessage(file);
15:
16: queueClient.Send(serviceBusMessage);
17:
18: }