On Premise SQL to Salesforce Integration
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.
https://howtobiztalk.files.wordpress.com/2017/03/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,
https://howtobiztalk.files.wordpress.com/2017/03/1.png?w=680
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
https://howtobiztalk.files.wordpress.com/2017/03/32.pnghttps://howtobiztalk.files.wordpress.com/2017/03/21.png
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))"
https://howtobiztalk.files.wordpress.com/2017/03/5.png?w=680
If there are Customers in the SQL Response, we loop through it and check if the Customer with the Same name in Salesforce.
https://howtobiztalk.files.wordpress.com/2017/03/13.png?w=680
"GetAccountFromSalesforce" shape is to get the Customer in Salesforce, the filter query does the name check in Salesforce. The code view looks like below,
https://howtobiztalk.files.wordpress.com/2017/03/6.png
Code view would be like this,
https://howtobiztalk.files.wordpress.com/2017/03/14.png?w=680
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.
https://howtobiztalk.files.wordpress.com/2017/03/15.png?w=680
If Customer not exists, then create a record in Salesforce and send an email.
https://howtobiztalk.files.wordpress.com/2017/03/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
https://howtobiztalk.files.wordpress.com/2017/03/16.png
SQL has 2 Customer but their names are present in Salesforce
https://howtobiztalk.files.wordpress.com/2017/03/9.png
SQL doesn't have any records and we get empty SQL Response
