Incremental refresh in Azure Data Factory

In this article we are going to do Incremental refresh for Account entity from Dynamics 365 CRM to Azure SQL.

Steps:

• Create Linked Service for Azure SQL and Dynamics 365 CRM and create a table in Azure SQL DB

• Now we will create pipeline, in the pipeline we have two blocks, one is for getting data from last modified date and other block is used to Copy data from D365 CRM to Azure SQL

• recordmark block:
  In the Query Section we are retrieving latest modified data, if there is no data in Azure SQL database for modified on column it will take ‘01-01-1999’ and start integrating data after this date. In preview section we can see the last modified data.

• Copy data from D365 CRM to ASQL block
• Source

In this block we will get last modified date from recordmark block and based on this it will fetch data for account, In the query section we are retrieving the account data from D365 CRM.

• Sink
  Now we will create UDT (User Defined Datatype) in Azure SQL, it will take care of update operation of existing records and insertion of new records based on “code” column using user defined stored procedure.

• Mapping
  Below is mapping details,

• once completed with above steps we are good to run Pipeline.
  • first we will check last modified date in Destination

• Now we will create Account in CRM

• Now we will run the pipeline.

• Now will check in destination, as we can see modified date/time is “2020-04-13 07:08:35.000”

• Now let us modify this record(Change Phone number to “987654321”

• Now run the pipeline again,

• Now we will check the data in Destination(Azure SQL Database)

In this way we can perform incremental refresh in Azure data factory.