How to read Salesforce reference field value using SOQL in SSIS / ODBC

[nayan]

PROBLEM

Sometime you need to access related nested fields (child object of lookup field) using SOQL in any 3rd party tools like
SSIS PowerPack - Salesforce Source or ODBC PowerPack - Salesforce Dirver.

SOLUTION

You can use the below syntax in SOQL to access nested objects in the Lookup field.

SELECT relation_name[.relation_name][.relation_name][.....].field_name FROM SomeTable

Examples:

SELECT Id, FirstName, Account.Owner.Name FROM Contact 

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

To craft the correct SOQL query, you need to know the Relationship Name for the Lookup field.

How to find the relationship name using Object Browser

To find lookup fields and its relationship name you can use ZappySys object browser,

First go to the Object browser UI (generally found on the Connection UI in the new version or if you are in SSIS then check SSIS Salesforce Source UI (Switch to Query Mode to see Object Browser in old version)).

The Lookup field has a different icon in the Salesforce Object Browser

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.
   Some more 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