5 min read

Importing Data from Salesforce.com to Microsoft CRM Online – Part 3

Welcome back!! I hope you are enjoying the powerful and easy to use Microsoft CRM in your day to day activities. This post is the last in series of the Importing data from Salesforce.com – Part 1 and Importing data from Salesforce.com – Part 2 blogs. In the previous post I had covered the entity mapping details of the two Salesforce.com maps. Here I will cover some specific scenarios which you might run into while Importing your Salesforce data. Let’s see them one by one for each of the data map.

Using Salesforce Map for Full-Data Export

Mapping ID fields in any record type (other than the 18 types present in the map)

If an ID is blank in Salesforce.com, such as the ParentID field in Account.csv, when you back up the files using the full-data export, Salesforce.com puts in a value of 000000000000000AAA. The provided Salesforce sample data map uses a replace transformation to empty this value for the 18 record types. If you are importing other record types, all values of 000000000000000AAA must be replaced by an empty field. Either edit the source files and replace 000000000000000AAA, or use a Replace transformation to replace all values in these fields as part of the import process. Here is an example of how to do this in the map:-

   1: <TransformationMap>
   2:           <TransformationTypeName>Microsoft.Crm.Transformations.Replace</TransformationTypeName>
   3:           <ProcessCode>Process</ProcessCode>
   4:           <InputParameterMaps>
   5:             <SingletonInputParameterMaps>
   6:               <SingletonInputParameterMap>
   7:                 <ParameterSequence>1</ParameterSequence>
   8:                 <DataTypeCode>Reference</DataTypeCode>
   9:                 <Data>ParentID</Data>
  10:               </SingletonInputParameterMap>
  11:               <SingletonInputParameterMap>
  12:                 <ParameterSequence>2</ParameterSequence>
  13:                 <DataTypeCode>Value</DataTypeCode>
  14:                 <Data>000000000000000AAA</Data>
  15:               </SingletonInputParameterMap>
  16:               <SingletonInputParameterMap>
  17:                 <ParameterSequence>3</ParameterSequence>
  18:                 <DataTypeCode>Value</DataTypeCode>
  19:                 <Data />
  20:               </SingletonInputParameterMap>
  21:             </SingletonInputParameterMaps>
  22:             <ArrayInputParameterMaps />
  23:           </InputParameterMaps>
  24:           <OutputParameterMaps>
  25:             <OutputParameterMap>
  26:               <ParameterSequence>1</ParameterSequence>
  27:               <Data>customerid</Data>
  28:               <LookupMaps>
  29:                 <LookupMap>
  30:                   <LookupType>System</LookupType>
  31:                   <LookupEntityName>account</LookupEntityName>
  32:                   <LookupAttributeName>accountid</LookupAttributeName>
  33:                   <ProcessCode>Process</ProcessCode>
  34:                 </LookupMap>
  35:                 <LookupMap>
  36:                   <LookupType>Source</LookupType>
  37:                   <LookupEntityName>Account</LookupEntityName>
  38:                   <LookupAttributeName>Id</LookupAttributeName>
  39:                   <ProcessCode>Process</ProcessCode>
  40:                 </LookupMap>
  41:               </LookupMaps>
  42:             </OutputParameterMap>
  43:           </OutputParameterMaps>
  44: </TransformationMap>

This is required, otherwise the records with value 000000000000000AAA in any ID column, will fail to import, with the error ‘lookup reference could not be resolved’. Since there exists no record whose ID is 000000000000000AAA.

Using Salesforce Map for Report Data Export

Rename the source data files

Rename the source data files to make sure that the file name is same as the Salesforce.com record type they represent. This will allow the Import Data Wizard to automatically figure out the Microsoft Dynamics CRM record types for your files. The file names should be as follows:-

  • Account.csv
  • Contact.csv
  • Lead.csv
  • Contract.csv
  • Opportunity.csv
  • Product2.csv
  • Pricebook2.csv
  • Task.csv
  • Event.csv
  • Solution.csv
  • Case.csv
  • CaseComment.csv
  • Campaign.csv
  • User.csv
  • Asset.csv

The file names are case-sensitive. If you use your own file names, you will need to specify the Microsoft Dynamics CRM record type on the Map Record Types page of the Import Data Wizard.

Importing a source file that contains two or more record types

When you import data into Microsoft Dynamics CRM, you must make sure that you have data for each record type (entity) in a separate file. However, at times, when you export data from Salesforce.com (using the report export), a single source file may contain data for two or more record types, such as contacts and accounts. In order to import such a data you need to follow the tips described in this blog post.

Clean any duplicate records in source files

Make sure that there are no records with duplicate names in your source files if the records refer to each other using names.

The data exported from the Reports area of Salesforce.com has its lookup references based on the primary names of the record types and not on the unique IDs (as in case of full-data export). Thus, if the source data contains records with same names, for example, same account name or same opportunity name and so on, then any records referring to these duplicate named records will fail during the import process with the "A duplicate lookup reference was found" error.

To solve this, the source data must be cleaned before using the Import Data Wizard, so that there are no records with the same primary names such as same Account Name in the Account.csv or same Opportunity Name in the Opportunity.csv, or same Last Name\First Name in the Contact.csv.

To do this in Microsoft Dynamics CRM Online, use the duplicate detection option in the last screen of the Import Data Wizard.

1. Before launching the Import Data Wizard, create appropriate duplicate detection rules in Microsoft Dynamics CRM. For example, Opportunity with the same name must be detected as duplicates; Contact with the same First\Last names must be detected as duplicate and so on.

2. Import the files one at a time with Allow Duplicates set to No on the Review Settings and Import Data page of the Import Data Wizard.

Any duplicate records are filtered out during the import process. That is, if you imported Account.csv with duplicate detection On for Account Name, only the accounts records with unique Account Name will be imported.

Now when you import the source files that have records referring to the record type with duplicate references, the Import Data Wizard will not find any duplicate references because the duplicates did not get imported. For example, now, if you import Contact.csv or Opportunity.csv that refers to Account.csv, the records in Contact.csv or Opportunity.csv will get successfully imported and will not give an error.

Remove any duplicate column headers

This can happen sometimes if your source file contains data from multiple record types. Importing such a file will give error in the Import Wizard. In this case you should edit the source data file and either rename the duplicate column headers to something unique or delete the duplicate columns.

I hope that you have found these series of blogs helpful in Importing Salesforce.com data into Microsoft CRM. Do respond back if you face any other issue while importing the data.

Thanks and Regards,

Nitin Mukhija