[SalesForce] Cannot import 15 character Salesforce Ids into a Data Extension

I'm experiencing very odd behavior on Marketing Cloud Accounts that have the Marketing Cloud Connector installed.

If I create a Data Extension which contains two or more fields, for example:

  • number [number field]
  • Id [text field, 50 character length]

Then if I import a value that uses the 15 character Salesforce Id syntax, then the import fails with the error:

ValidationErrors: CaseSensitiveSalesforceID: 5

Refer to the data I'm uploading below. Nothing special here and all the values are unique.

number,Id
37784,00390000009M2Dt
2028359,0039000001pgE55
2508,00390000009Lpjh
1835372,003900000153YEN
1835386,003900000153YEb

It does not matter if there are the following in the Data Extension:

  1. Number field as the primary key
  2. A compound primary key (using both fields)
  3. No primary keys
  4. If I select 'overwrite' on the import activity
  5. If I change the Id field name to something else

Each time the import fails with the same error. However, if I prefix the 15 character Salesforce Id with 'X', Marketing Cloud will not validate this as a Salesforce Id and the import is successful. For example, using the following data:

number,Id
37784,X00390000009M2Dt
2028359,X0039000001pgE55
2508,X00390000009Lpjh
1835372,X003900000153YEN
1835386X,003900000153YEb

This issue (where Marketing Cloud does not import any values in a 15 digit Salesforce format) only appears to occur with accounts that have the Salesforce Connector provisioned. I've found a help article on this behavior but none of the solutions work.

I understand that in Salesforce, 15 character IDs are case sensitive. Salesforce also has 18 character IDs which are case insensitive, but I really want to use the 15 character Id, and would prefer not to use the CASESAFEID() function to create a 18 character Id in Sales Cloud.

Has anyone else experienced this before, and if so, do you know of a workaround?

Best Answer

I've tried a few tests. The only workarounds I have found was to modify the pattern found in the Id field.

Options:

  1. Change the pattern of the value in the Id field like you did by prefixing with an 'X'.
  2. Spitting the Id into two field. In my test, I create a 3rd column named prefix and modified the data so the '003' portion of the Id imported into this field.

sample data:

number,prefix,id
37784,003,90000009M2Dt
2028359,003,9000001pgE55
2508,003,90000009Lpjh
1835372,003,900000153YEN
1835386,003,900000153YEb
Related Topic