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
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.
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.
We now just sync the changes into the CRM Account entity.
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.
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.
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.