Upsert Salesforce Data Using External Id in SSIS

You can use the CData SSIS Component for Salesforce to upsert data to Salesforce. An upsert will automatically update an existing record or insert a new one based on a predefined external Id field on your Salesforce objects. The external Id field is the name of the column that is used to decide if the record already exists or it should be created. For example, external Id could be the Name column in the Account table. You can follow the procedure in this article to execute an upsert based on an external Id.

1. First, set up the data source. Salesforce is used in this example, but any source will work for this process.
2. Drag a Derived Column transformation component from the Data Flow Transformations section of the toolbox onto the data flow.
3. 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".
4. Add the Salesforce destination component. In the Salesforce connection manager set your credentials used to log into Salesforce. In addition to specifying your credentials, set the Pseudo Column to *=*. Setting the Pseudo Column to *=* tells the provider to include the pseudo column "ExternalIdColumn" in the schema of the destination table. You will use this column during the mapping below.
5. 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.