Dynamics CRM 2011 Import Contacts and Accounts

Update: Simego DS3 has been updated to allow a much easier drag and Drop import and lookup process.

This (no code) post shows how to use Data Sync Studio and Lookup Columns to import Accounts and Contacts from SQL into Dynamics CRM and keep the relationship between Account and Contact.

Our SQL Export Database consists of 2 tables “Account” and “Contact” these 2 tables are linked via a Foreign Key Contact.AccountID –> Account.ID.

Account SQL Table

Contact SQL Table

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 1 - 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

Schema Map

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

Step 2 – 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

SQL Schema Map

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 AccountID and the accountnumber in CRM. Select the AccountID column in the Data source window and click the “Add Lookup” button.

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

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

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

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

and that's it you can sync up the data and see that your contacts are correctly related to your accounts.

You can download and use a fully featured version of Data Sync Studio for 15 Days to see how this SQL and CRM solution works.

Click here to go to the Downloads