-
First, set up the data source. A Salesforce source is used in this example, but any source will work for this process.
-
Drag a Derived Column transformation component from the Data Flow Transformations section of the toolbox onto the data flow.
-
Double-click the Derived Column to open the Transformation Editor and add a new column. Set the Derived Column Name to the name of the new column (MyExternalIdColumn in this example). Select the "add as new column" option in the Derived Column field. Set the Expression field to the name of the external Id column in the Salesforce destination table, including quotes. This example uses "external_id_c__c".
-
Add the Salesforce destination component. In the Salesforce connection manager, in addition to your credentials used to log in to Salesforce, set the PseudoColumn advanced property to *=*. Setting the PseudoColumn to *=* tells the component to include the pseudo column “ExternalIdColumn” in the schema of the destination table. You will use this column during the mapping below.
-
In the Salesforce destination component, specify the table that will be used for the upsert operation (i.e., the destination table). In the mapping section, map the derived column that you created in step #3 (in this example, MyExternalIdColumn) to “ExternalIdColumn” pseudocolum mentioned in step#4. Additionally, map the column from the source table to the Salesforce external id column of the destination table. In this example, we mapped the “external_ID__c” from the source to “external_id_c__c” in the destination table.
6. You can now execute the task to UPSERT data to Salesforce based on the Salesforce external Id column of the destination table.
