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. Next, double-click the Derived Column to open the Transformation Editor and add a new column.
3. 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 field in Salesforce for this table, including quotes. This example uses "ExternId__c".
4. Add the Salesforce destination component. Create a new connection manager and define the connection properties. In addition to specifying your credentials used to log in to Salesforce, set the Pseudo Column to *=*. Setting the Pseudo Column to *=* tells the provider to include the special column External Id in the schema of the table.
5. In the Mappings section, add a new mapping for the column you created in the step above. In this example, the Input Column is set to MyExternalIdColumn.
6. Select a table and select Upsert in the Action menu.
7. You can now UPSERT data to Salesforce based on the external Id column.