An UPSERT statement updates an existing record or creates a new record if an existing record is not identified.

Configuring Upserts

Upserts can only be performed on a field explicitly defined in Salesforce to be an external key. You need to provide the name of this field in a column called ExternalIdColumn, as shown in the following query:

  UPSERT INTO Lead (FirstName, LastName, Company, External_Id_Column__c, ExternalIdColumn) 
  VALUES ('Bob', 'Thorton', 'Universal Pictures', 12345, 'External_Id_Column__c')

In order for the ExternalIdColumn to show up, modify the Salesforce connection properties to set the PseudoColumns field to the value '*=*' without the quotes.

An UPSERT statement updates an existing record or create a new record if an existing record is not identified.

UPSERT Syntax

The UPSERT syntax is the same as for insert. Salesforce uses the input provided in the VALUES clause to determine whether the record already exists. If the record does not exist, all columns required to insert the record must be specified. See Data Model for any table-specific information.

UPSERT INTO <table_name> 
( <column_reference> [ , ... ] )
VALUES 
( { <expression> | NULL } [ , ... ] ) 
  

<expression> ::=
  | @ <parameter> 
  | ?
  | <literal>

SSIS-Specific Limitations for UPSERT

Using an External ID

UPSERT operations in Salesforce require a field explicitly defined as an External ID. In SSIS, the field used for matching must be specified via the ExternalIdColumn pseudo-column. This value must match the name of the column in the Salesforce table that is marked as an External ID.

Using the Id Column as a Key

If you choose to map the Id column instead of using an External ID, the behavior depends on the input value:

• NULL value: Inserts a new record
• Valid Salesforce ID: Updates the record
• Invalid or missing ID: Results in an error

To execute UPDATE or DELETE operations, the Salesforce Id must be included in the destination mapping. Otherwise, the operation fails with an error such as, "The primary key must be specified to delete a row."

No SQL Command Mode

The SSIS Salesforce Destination Component does not support a SQL Command data access mode. All operations must be configured using the UI and table mappings only.