Duplicate key error returned by import of Salesforce objects and reports to a Marketing Cloud data extension
|Knowledge Article Number||000213103|
Imports from Salesforce objects and reports to a data extension fail due to duplicate key errors when the ContactID or LeadID field is selected as a primary key. This occurs because Salesforce uses a case-sensitive identifier and Marketing Cloud is not case sensitive.
Salesforce uses a case-sensitive, text-based, 15-digit unique identifier for their ID fields such as AccountID, ContactID, and LeadID. Since they are case-sensitive, you can use 1A and 1a to signify two distinct records. Because Marketing Cloud is not case sensitive, 1A and 1a are read as the same value. As a result, when you attempt to import these two values to a field set as a primary key, a duplicate key error is returned because they are interpreted as the same value.
There are several options for resolving this:
For example, if you have two records with the following values:
These are interpreted as distinct records because the values for EmailAddress are different.
Salesforce has a function available for custom fields called CASESAFEID(Id) that you can use to convert the case-sensitive, 15-digit unique identifier to a case-insensitive ,18-digit unique identifier. If this field is included in the report when it is created within Salesforce, you can include it when the data extension is configured for the Import Activity definition within the Marketing Cloud and set it as a primary key. Because this 18-digit ID is case-insensitive, there won't be any duplicate key errors returned when it is imported.
Related TopicHow to convert a 15 character id to a 18 character id