How To

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:
Account Data Preview
Contact Data:
Contact Data Preview

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'.

Account Schema Map

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.

Contact Schema Map

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.

AccountID Lookup

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.

Schema Map

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.
Contacts Updated in CRM

Properties

CommandTimeout

The command timeout.

ConnectionString

The connection string used to connect to the database.

SourceTable

The object in the database to use.