Use Logic App to Upsert D365 Data into Azure SQL

Here's one approach to insert or update (upsert) data from Dynamics 365 (CE) or Dataverse into an Azure SQL table using Logic Apps:

1. In Azure SQL...

2. Create a stored procedure that the Logic App will call to perform the upsert

   1. Here's a page that mentions the best approach for this: Please stop using this UPSERT anti-pattern - SQLPerformance.com

3. In the Logic App...

4. Create a Recurrence trigger (or other trigger depending on your use case)

5. Select and configure a Dataverse connector to retrieve the needed data

   1. If you need to retrieve more than 512 rows from D365 (the default maximum number of rows):

      1. Set the Top Count to a higher number of rows than what you're expecting. You can specify a number up to 100,000.

      2. Edit the Settings for the action and turn on Pagination and provide a value for Threshold (e.g., specify the same number you provided for Top Count).

   2. If you want the connector to return specific columns, edit the code for the Logic App and include a $select parameter. See this blog article for an example.

6. Add an Azure SQL action. Select the stored procedure and map to any parameters.

   1. Note: If you're querying a lot of records from D365, your Logic App might error on the Azure SQL step with error "429 - Rate limit is exceeded. Try again in N seconds.". One way to work around this is to edit the Settings for the Azure SQL action and edit the Retry Policy so that the connector will retry on a set interval. Example: Fixed Interval, 10 times with an interval of PT10S.

Related tips:

• To avoid upserting the same D365 records into SQL, use a setting or other data to apply a filter to the source D365 records. For example, if you store the modifiedon for the D365 records you retrieve then you can use that date/time in future executions of the Logic App to only return the latest records.

• Create a primary key on the Azure SQL table so that the stored procedure runs as quickly as possible.