Introduction


Hello friends, in this article I would like to walk through a Integration Scenario and explain some capabilities of LogicApp.

In this scenario, we receive Customer records from On-Premise SQL database by executing "GetCustomers" Stored Procedure, and post one by one into Salesforce. Before posting a new Customer, we should check if the Customer is already present in Salesforce.

SQL Setup


Our Customer Table in On-Premise table would be like the below,

CREATE TABLE [dbo].[Customers](
[CustomerName] [nchar](300) NULL,
[BillingCity] [nchar](100) NOT NULL,
[BillingStreet] [nchar](100) NOT NULL,
[BillingState] [nchar](100) NOT NULL,
[BillingPostalCode] [nchar](10) NOT NULL,
[status] [bit] NULL
) ON [PRIMARY]

GetCustomers Stored Procedure

CREATE procedure [dbo].[GetCustomers]  as
SELECT TOP 1000 LTRIM(RTRIM([CustomerName])) as CustomerName
      ,LTRIM(RTRIM([BillingCity])) as BillingCity
      ,LTRIM(RTRIM([BillingStreet])) as BillingStreet
      ,LTRIM(RTRIM([BillingState])) as BillingState
      ,LTRIM(RTRIM([BillingPostalCode])) as BillingPostalCode
  FROM [myOwn].[dbo].[Customers]
 
  where [status] = 0

I have used TRIM functions inside the Stored Procedure because there are no trim functions yet in Logic App.

Customer Table JSON format. In Logic App, we receive the data in this format.

4.PNG

Salesforce Setup


We don't need any specific setup in Salesforce because Logic App connector takes care of all REST based communication including OAUTH authentication.(We just need to provide Salesforce Username & Password)

Logic App


Our Logic App would look like below,

1.PNG

Flow Description

Recurrence shape is responsible for triggering our LA flow for every 3 mins

"Exec OnPrem StoreProcedure" shape executes On-Prem Stored Procedure. Connecting to On-Premise is possible via "On-premise data gateway". More Details are here

32

ChkIfAnyCusotomers Condition shape which helps us to check if there is any Customers in the SQL Request. The below command returns false if no data found. Here we check the body of SQL Response to see if there is a Table1 column, if it is empty the flow ends here.

"@not(equals(empty(body('Exec_OnPrem_StoreProcedure')['ResultSets']?['Table1']), true))"

5.PNG

If there are Customers in the SQL Response, we loop through it and check if the Customer with the Same name in Salesforce.

13.PNG

"GetAccountFromSalesforce" shape is to get the Customer in Salesforce, the filter query does the name check in Salesforce. The code view looks like below,

6.PNG

Code view would be like this,

14.PNG

Now we have to check if no Customer with the Same name is available. for that, we have a condition shape that check if the JSON has a valid Customer or empty.

15.PNG

If Customer not exists, then create a record in Salesforce and send an email.

7.PNG

OK, we are done with out Logic App and now we shall see some output scenarios.

Output Scenarios

SQL has 2 Customers and their names aren't there in Salesforce

16.PNG

SQL has 2 Customer but their names are present in Salesforce

9.PNG

SQL doesn't have any records and we get empty SQL Response

12.PNG