3

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?

3 Answers 3

4

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
1
  • @EliotHarper I've added an answer with a more straightforward solution
    – joe
    Nov 11, 2016 at 11:53
1

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

3
  • I tried this approach but I wasn't able to import a Salesforce lead id data in data extension. I was using import CSV method and that may be a reason why changing column header didn't worked for me. Jan 15, 2017 at 7:02
  • @GajendraSingh Is the column name in the Salesforce Data Extension named Contact ID or Lead ID as well?
    – joe
    Jan 16, 2017 at 9:56
  • I faced a similar error and issue this afternoon. My file was not a Salesforce Report originally, so I ended up using help.magentrix.com/aspx/ConvertSFID to convert the 15-char IDs to 18-char IDs and then the import worked with the csv and data extension field name being LEAD_ID. Mar 15, 2017 at 21:09
1

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .