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.
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 table and procedures
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)
Add SQL Server stored procedure schema (PurchaseOrderDB.xsd)
Add XML Transformation Map
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
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