Cannot import 15 character Salesforce Ids into a Data Extension

Question

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?

Answer 1

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

Answer 2

I faced the same issue and found that Exacttarget expects the column name to be exactly either Contact ID or Lead ID

If the column name of your report has anything other than the above, you could change it to either Contact ID or Lead ID by creating a custom report type. This would resolve the issue.

The report displays the 15 character ID when viewed in Salesforce but the data imported into Exacttarget has the 18 character ID value and the error disappears

Answer 3

Just a thought from my side.. if you have access to the 15 char ID, you should also have access to 18 char id, which - generally speaking - I'd recommend to use.

The reason is, that the ID's you get when you synchronise your data extensions with Salesforce Core, contain 18 digit ID fields.

Thus, if the data you are importing needs to be related to the contact model using the standard way of doing it - it requires the 18 char Id's.

The CaseSafeId won't help with data relationships etc. to my knowledge, so sticking to the 18 char id is a safer build.