Logic App / On-Premise SQL connectivity with & without Integration Service Environment (ISE) – Part 1

In the following blog post’s we will implement a scenario and check how it behaves with and without Integration Service Environment.

We would be focusing on the below points

• Implementation Approach
• Execution time
• Challenges faced etc.

Artifacts Used – VNet, Subnet, SQL Virtual Machine, Logic Apps, Integration Service Environment, On-Premise Data Gateway

Scenario – An XML file with repeating records (50 records)will be fetched for the File Share, De-batched and individual records will be send across to a SQL Table.

Approach 1 – Without Integration Service Environment (ISE)

This approach was quite straight forwards and did not face much challenges.

• Created a VNet/Subnet
• Create SQL Server VM and added it to my default subnet
• Installed On-Premise Data Gateway
• Created a file-share, SQL Database and Table on that VM
• Logic App (de-batch and insert individual file to SQL table)

The input XML will look like below with 50 Customer Information.

You can refer to the following link on how to create a VNET, SUBNET and associate a VM to it. (Please note you need to create a SQL Server VM).

https://azurebiztalkread.wordpress.com/2018/09/08/70-535-series-virtual-machine/

Once your VM is up and running please install On-premise data gateway on that SQL Server VM. Refer to the following link for download and installation

https://docs.microsoft.com/en-us/azure/analysis-services/analysis-services-gateway-install

Once the setup is completed Login to the Azure portal and create a connection to your on-premise data gateway.

Once the VM and Gateway is in place now you are ready to connect your On-premise with Logic App. Let’s start building the Logic App. Please note we would be building the similar scenario using Integration Service Environment (ISE) in later post.

Steps as below –

• When a file is added to a file-share (XML with 50 records)
• Get content of the file
• Split the file using XPath (De-batching)

https://azurebiztalkread.wordpress.com/2018/05/26/debatching-using-azure-logic-apps/

• Insert each record to the SQL Table

Please note for this scenario we will be using the NON ISE SQL Connector. The connectivity with the On-premise machine will be via Azure Data Gateway. While in case of Integration Service Environment we don’t need to use the Azure Data Gateway rather directly use ISE connector. We would see more about it in later post.

Before connecting to the SQL or File-share you must configure the below setting’s for both connector File System and SQL Server.

Now drop the XML to the file share and it must split and insert 50 individual records to SQL.

• The Execution time for the logic App was approximately 12.65 seconds.

There was not much challenges faced while setting this up but when the same was setup using Integration Service environment there were couple of challenges faced which will be discussed in Part 2 of this post.