CRM- SQL server integration using Logic apps

It has been a long time since my last blog. I am thinking of doing a few more blogs about Logic Apps this year.  The best thing about Logic Apps are the number of connectors available out of the box to connect to different applications. With the enterprise integration account, Logic Apps can implement enterprise patterns and scenarios similar to BizTalk server.

This is the first blog of the series. In this blog, I will integrate D365 online with SQL server on premise using Logic Apps.

Solution components

Steps

This is very simple integration scenario. This Logic App will poll Dynamics 365 for new contact records and insert a row in the on premise SQL Server table named Customer.

1. Setup an on-premise Data Gateway. The following link provides the step by step instruction to setup a gateway.
   https://docs.microsoft.com/en-us/azure/logic-apps/logic-apps-gateway-connection
   Note: Consider latency while selecting the region for the gateway
2. Create a new Logic App. Check my old blog for screenshots of steps to get to the Logic App designer.
3. Select a Dynamics 365 connector and select Dynamics 365 - When a new record is created.
   
   
   
4. Enter the username and password to connect to your D365 organisation.
5. Select the Organization Name, Entity Name, Interval and Frequency as shown below. The fields Interval and Frequency are very important. It will define how often the Logic App will poll D365 for new records.
   
   
   
6. Add a New step and choose Add an action.
   
7. Choose SQL Server >> SQL Server - Insert row.
   
   
   
8. Enter the connection information as shown in the screen shot below. Make sure On Premise data gateway is checked and select the gateway created in step 1 of the blog.
   
   
   
9. If the connection is successful, select the name of the table and map the column names with the CRM field names.
   
   
   
10. The output of this action can be used to perform further action. For this blog, I am not doing anything with the output. Save the Logic App and test it by creating a record in CRM.
11. I ran some tests and here is the Run history of the app. After the initial run, the average time to process a record is sub 3 seconds, which is quite good considering my machine is Australia and Logic App is hosted in the US West region.