SSIS Salesforce Destination - Update / Insert with Lookup Fields (Text to Id)

Introduction

In this article we will cover how to use SSIS Salesforce Destination to perform Insert / update / upsert operations in Salesforce which involves updating Lookup Fields.

Sometimes you have names (i.e. text) rather than Id when you map Lookup Fields in the Salesforce destination. In such case you have to perform extra steps to get Id of the related record from other table (i.e. Lookup field).

Step-By-Step

Let's look at how to handle Lookups in Salesforce Insert, Update or Upsert Operations.

Basically you cannot set Text value in Lookup field so you have to find Id or related Record by Lookup Operation. SSIS makes it easy by providing Two important native transforms

• Cache Transform
• Lookup Transform

Now let's look at steps to Lookup data based on Text (i.e Name Field).  

In below example we have created two tables (Applicants and Application_Parents). In Applicants table we have a Lookup field which refers to Application_Parents like below.

Now, lets see how to insert into Salesforce with Lookup field mapped in target.

1. Create Data Flows and connect them as below
   
   
2. Let's now create a SOQL query to read Lookup data Go to first data flow and lets drag ZS Salesforce Source and configure as below. As you see
   
   
   
   
3. Click OK to save Salesforce Source
4. Now drag Cache Transform and connect Source to Cache
5. Double click Cache Transform and Configure like below. Click New to create new Cache Connection. Give some name to Connection.
   
   
   
6. Now go to Column tab and make sure you set Name as Index = 1
   
   
   
   
7. Click OK to save Connection.
8. Now back to Cache Transform Click on Mappings tab and make sure mappings.
   
   
9. Click OK to save Cache Transform and now Drag Trash destination from SSIS Toolbox and connect Cache to Trans destination to complete the flow.
   
   
10. Now go back to second data flow. You can now you can drag your source (e.g. OLEDB Source) which will generate data for Salesforce Destination. In our example we have used CSV Source for demo which has two fields like below.
    
    
    
    
11.   Now drag Lookup Transform from SSIS Toolbox and connect your Source to Lookup and Configure like below. In this example we will redirect Bad rows (Not matched) but you can leave default if you don't want it (i.e. Fail component if match not found).
    
    
    
    
    
    
    
    
12. Click on Columns and Join column by which you want to perform Lookup and Check other column from Cache which like to output. In our case we are doing Lookup by Name and getting Id back. Alias output column correctly so easy to identify in the destination mapping screen.
    
    
13. Now drag Salesforce destination and select connection of first tab, select table on the second tab (For Upsert Operation select Upsert Key too)
    
    
14. Now go to Mapping Tab and map fields like below
    
    
    
15. That's it now your flow will look like below.
    
    
    
    
16. Run the package and you will see your Lookup field is correctly set.