› Dynamics CRM › Incremental load using Change tracking feature of CRM 2015 Update 1 with SSIS Integration toolkit – Kingswaysoft

Incremental load using Change tracking feature of CRM 2015 Update 1 with SSIS Integration toolkit – Kingswaysoft

 3 Comments

Change tracking is a new feature available in CRM update 1 that can help in keeping the data synchronized in source and target systems by detecting the data changes since the data was initially created or last synchronized.

Change tracking is used for incremental load

STEPS:

  1. Before we begin to implement change tracking, we need to enable it on the entity for which we need to track changes

    Customizations → Entity → Enable change tracking

  2. 1

  3. We need to maintain a table in Sql so that the output tokens can be saved for future purpose which will help to maintain a version for the records in the form of tokens

    [Note: The tokens can be maintained in a file also]

    Let’s start by creating the SSIS package using SSIS integration toolkit for Dynamics CRM-Kingswaysoft

    Flow of package will be:

  4. 2

  5. Drag and drop the Execute Sql task which will be used to fetch the tokens from Sql

    [Note: In case of initial migration, the token will be NULL]
    3
    Since initially the table will be empty we need to handle the NULL scenario

    1. Create a variable for maintaining the count of rows for the table(we need to store the result set in this variable created)
    2. 4

    3. If the count=0 (1st time migration), it goes to a sequence container passing the input token as NULL
    4. If the count !=0 , it goes to a sequence container passing the input token as the last inserted token in the table
  6. Let’s consider we migrating the data for the 1st time

    The sequence container will contain a data flow task (having the source and target CRM components) and Execute Sql task (storing the new token to Sql)

    Data Flow task:

    1. Drag the CRM source component
    2. Set up the connection, select source type as ‘Entity changes’, mention the entity and define the input and output token variables
    3. [Note: In this scenario the Input token will be NULL as it is 1st time migration]5

    Now we drag two CRM destination components

    1. CRM Primary Input (Tracking the Updates and Inserts)
    2. CRM Secondary Input (Tracking the Deletes)
    1. For CRM Primary Input destination component, set up the connection, action as ‘UPSERT’, mention the destination entity and upsert matching criteria
    2. 6

    3. For CRM Secondary Input destination component, set up the connection, action as ‘DELETE’ and mention the destination entity
    4. 7

    Execute Sql task:

    1. We need to store the output tokens in Sql, using Execute Sql task
    2. 8

    3. In parameter mapping, we mention the input and output tokens variables that we get from the Data flow task
    4. 9

    1. Let’s consider the scenario where we already have the data set up
    2. The sequence container will contain a Execute Sql task (for fetching the last output token from Sql), data flow task (having the source and target CRM components) and Execute Sql task (storing the new token to Sql)

      Execute Sql task:

      1. We need to fetch the output token from Sql
      2. 10

      3. The result set can be stored in the Input token variable which we pass to the CRM source component
      4. 11

      The steps for data flow task and the execute Sql task are the same as mentioned earlier

    3. The final package will look like this:
      12The package can be scheduled on a daily basis thus keeping the data in sync in the source and target systems.

     


    Liked it? Share it.

    3 thoughts on “Incremental load using Change tracking feature of CRM 2015 Update 1 with SSIS Integration toolkit – Kingswaysoft”

    1. Hi there,
      what I am trying to achieve is to keep the records in sync between Navision and CRM. We are able to migrate the data from CRM to Nav system. But I am not able to figure out how to acknowledge the successful data move, so that CRM system can know which of the records have been successfully migrated. Basically, we want something similar to three-way handshakee like in TCP.
      For example,
      There are Sales Orders and Orders in CRM system, we have moved entities records to navision system. Now we need to somehow inform the CRM that those records have been successfully processed. So that both of the systems are in sync.

      Is there any provision in the tool for this to implement? Can you please suggest me on how to proceed with this?

      Best Regards,
      devp

    Leave a Reply

    Your email address will not be published. Required fields are marked *

    Time limit is exhausted. Please reload the CAPTCHA.