I've inherited an SSIS 2008R2 + Task Factory package where the data source is Salesforce.com This source has id columns that are nvarchar(18). Most of them are collated SQL_Latin1_General_CP1_CI_AS, but there are a couple that are SQL_Latin1_General_CP1_CS_AS and Latin1_General_CS_AS.

Would anyone know what controls the collations of PK's / FK's in Salesforce.com?


The developer or tool that built the database decided, and if multiple developers were responsible, that would explain the differences. There are only two "correct" data types for ID values. Older databases would probably use a Latin 1 CS char(15) variant, or Latin 1 CI char(18) for newer databases. Anything else would not be the correct data type.

  • Thanks. Does that mean that newer apps/databases do not require case sensitivity for the ID columns? Also, just to satisfy any paranoia I have, are there any Salesforce UI settings that would affect ID column collation? – Jim Horn Nov 21 '14 at 14:36
  • The extra three characters provide uniqueness without sensitivity, so it's no longer required so long as you're using the full 18 character value. If you're running reports, and exporting them, you'd still want 15 character case sensitive values. The latest versions of the API always use the 18 character version, while the UI and the very old API versions use the 15 character version. There's no setting we can set to choose which type to use. – sfdcfox Nov 21 '14 at 14:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.