How to set salesforce lookup field using external Id (update / insert) in SSIS / ODBC

[nayan]

PROBLEM

Sometimes, you need to set the Salesforce lookup (reference field) value using an external ID rather than the internal ID of the Salesforce account. When you sync data from other systems, the External ID is used rather than the internal ID. For example, you would like to update the Account associated with the Contact Record using the AccountNumber (custom external ID) field rather than the internal ID of the Account record.

For example, rather than below

UPDATE Contact 
SET AccountId = '003Dn00000CsrdTIAR' 
Where Id='003Dn00000CsrdTIAR'

You want something like below (assuming AccountNumber is an external ID)

UPDATE Contact 
SET [Account.AccountNumber__c] = 'AC001' 
Where Id='003Dn00000CsrdTIAR'

SOLUTION

To solve this you need the latest version of SSIS PowerPack - Salesforce Destination (5.4.1 or higher) or ODBC PowerPack Salesforce Dirver (1.8.4 or higher).

Find the external Id field name you would like to use for the update

The first thing is you need to know the exact name of your field, which is External ID. Click here to learn steps to create / view external id field

For ODBC - Update / Insert Reference Field Value using External ID

Here is an example how would you update lookup field using external id field in ZappySys Salesforce Driver

UPDATE Contact 
SET [Owner.UserId__c(User)]='USR0001' 
WHERE Id IN ('0014N00001hTNEEQA4')
/*
where 'Owner' is relationship name and 'User' is the related object name which contains 'UserId__c' field 
(Note: __c suffix is used for custom field). This related object name needs to be in parentheses.

If you do not supply this name then Relationship name without '__r' suffix is used as object name 
but if your relation name is not same as the referenced object name then you must specify that.
*/

Another example
In the example below,

1. We create a Custom Table called Country
2. we have added a custom lookup field (Country) in the Account table.
3. Added CountryCode column in Country table.
4. Finally, we issued an Update statement to set the Account country using Lookup reference (external ID field). Notice that the “__r” suffix usually indicates the Custom relationship name and __c custom field or custom object (i.e. table).

UPDATE Account 
SET [Country__r.CountryCode__c(Country__c)] ='US'
Where Id='001Dn00000ITL8SIAX'

• Make sure to use brackets around the reference field (any time you have a dot in your name, you must use brackets (e.g., [Account.SomeField] )
• Related object name is optional but highly recommended.

How to find the relationship name and referenced object name

To find the exact syntax for lookup field name in UPDATE / INSERT statement, go to the Object browser (found on Connection UI in the new version else check SSIS Salesforce Source UI).

For SSIS - Update / Insert Reference Field Value using External ID

Here is how to set lookup field using external id in SSIS.

You can open SSIS Salesforce Destination and navigate to external metadata for Input.

How to read Salesforce reference field value using SOQL (Lookup / Nested Child Object)

Now let’s look at how to read reference field value using SOQL.

1. Findout relationship name of lookup field using below method.
2. Now you can use relationship name to navigate field you like to read using SOQL.
   Examples:

SELECT Id, Account.Owner.Name from Contact
SELECT Id, Account.Country__r.Owner.Name from Contact
SELECT Id, FirstName, Account.Id, Account.PK__c,Account.Country__r.Name 
FROM Contact WHERE Id='003Dn00000CsrdfIAB'

Read nested fields in Salesforce ODBC Driver UI

Read nested fields in SSIS Salesforce Source UI

For Older version - How to Update Lookup using External ID

If you have an older version, either in ODBC Salesforce Driver or SSIS PowerPack, then try the following workarounds.

For ODBC - Use SOURCE clause along with BulkApiMode =1 and use [Relation_Name.Externa_Field]

In old version you can use Relation_Name.External_Id_Field as below and it should work fine.

UPDATE Contact
SOURCE('ODBC' --external driver type (MSSQL, ODBC, OLEDB)
      ,'Driver={ZappySys CSV Driver}'  --driver specific connection string (i.e. MSSQL, ODBC, OLEDB)
      ,'select ''003Dn00000CsrdfIAB'' as Id, ''K1'' as [Account.PK__c]'
      --where Account is relation name some times its ObjectName__r if its custom Lookup field. PK__c is Custom ExternalId added in Account Table
      )
WITH(
       Output=1 /*Other values can be Output='*' , Output=1 , Output=0 , Output='Col1,Col2...ColN'.  When Output option is supplied then error is not thrown but you can capture status and message in __RowStatus and __ErrorMessage output columns*/ 
      ,EnableBulkMode=1 --use Job Style Bulk API (uses 10000 rows per batch rather than 200) 
)

For SSIS - Use BulkApiMode =True and add ExternalMeta columns
For SSIS - You have to manually edit the DTSX file due to a bug in Advanced UI.
Add External Meta Column and Map it to Some Input Column with ID. This method is not fully tested (More information coming soon)