United Kingdom +44 (0) 1424 863 450   hello@simego.com

Import Accounts and Contacts into Dynamics CRM

How to Import Accounts and Contacts into Dynamics CRM


9 May 2012

Step 1: Prepare the source SQL tables

For this example we have 2 tables “Account” and “Contact”, these 2 tables are linked via a Foreign Key Contact.AccountID –> Account.ID.

Account SQL Table

image

Contact SQL Table

image

Dynamics CRM doesn’t work with Integer keys 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 Guids. We are using Data Synchronisation Studio which can manage the GUIDs without making any changes to your SQL Database or Dynamics CRM.

Step 2 - Import your Accounts from SQL

This is an easy Data Sync project where you simply import your accounts table into the Accounts entity mapping name to name and id to accountnumber.

Project in Data Synchronisation Studio

image

Schema Map

image

We now just sync the changes into the CRM Account entity.

Step 3 – Import your Contacts

This step requires mapping the Contact table to the Contact entity in CRM mapping the ID value from the SQL Table to the ExternalUserIdentifier column in CRM.

Project

image

SQL Schema Map

image

 

Step 4: Define a Lookup between AccountID and the accountnumber in CRM.

We need one more trick to automatically associate the Contact with the imported Account record in Dynamics CRM. For this we define a Lookup between AccountIDand the accountnumber in CRM.

Select the AccountID column in the Data source window and click the “Add Lookup” button.

image

Next configure the Lookup Data source to connect back to the Dynamics CRM account entity.

image

Next define the Lookup relationship by selecting accountnumber in the Target Column field.

image

Now you can use all the columns from the related Account CRM entity in your project.

image

We now need to map Lookup1_accountid to parentcustomerid to create the relationship in Dynamics CRM.

image

Step 5: Preview and Synchronise the Contacts

image


|
Can't find the answers you're looking for?