Importing Data from SQL Tables to Dynamics CRM

Data Sync enables simple migration/ importing of data from sources such as SQL Server.

SQL Tables

In this example we will be using two tables 'Account' and 'Contact', which are linked by a foreign key 'Contact.AccountID' to 'Account.ID'.

Account Data:

Contact Data:

Dynamics CRM does not work with integer keys, as it uses guids for the primary key. In many cases it may be impossible to change the source database to update each record with a guid. Data Sync provides a workaround, where the system can manage the guids without making any changes to the SQL Database or Dynamics CRM.

Importing the Accounts

To import the accounts we need to create a simple Data Sync project linking the Accounts table to the Accounts entity in CRM. This is achieved by mapping 'Name' to 'name' and 'ID' to 'accountnumber'.

Compare A to B and synchronise the changes into the CRM account entity.

Importing the Contacts

To import the contacts we will need to create a data sync project where the Contact table is linked to the Contact entity in CRM. This is achieved by mapping 'ID' from the SQL table to 'externaluseridentifier' in CRM.

Compare A to B and synchronise the changes into the CRM contact entity.

Define the Lookup

To automatically associate the Contact with the imported Account record in CRM, we will need to define a lookup between 'AccountID' and the 'accountnumber' in CRM.

Drag and drop the CRM account entity onto the AccountID column in the data source window, and then define the relationship by selecting 'accountnumber' in the target column field.

This will enable access to all columns related to the Account entity for use in your project.

Add 'Lookup1_accountid' to the schema map and map this to 'parentcustomerid' in the target to create the relationship.

You can now compare and synchronise the contacts. The accounts will now be linked to the customer in Dynamics CRM, which appears as 'Company Name' in the example below.

Properties

CommandTimeout

The command timeout.

ConnectionString

The connection string used to connect to the database.

SourceTable

The object in the database to use.