Upsert

Parent Previous Next


Upsert Destination



Azure Users

Please see the Azure Support page for more information before using this component with Azure databases.



  Destination Connection Manager - Select or create the connection manager that will be used to upsert data into the destination.

 Table Access Mode - Table access mode allows the user to define how the destination table name will be selected. There are two modes

o  Table - This mode will allow you to choose the table in the "Destination Table Name" drop down

o  TableFromVariable - This mode will allow you to choose a variable which contains the destination table name in the "Choose Table Variable" drop down, only visible when "TableFromVariable" is selected.

o  Destination Table Name / Choose Table Variable - Choose either the destination table or the table variable

o  Enable Identity Insert - This option is automatically selected when Upsert detects that the destination table contains an identity column. If the selected source for Upsert does not contain a column that will insert data into the identity column, uncheck this selection. If the source will contain at least some rows that will be inserted into the identity column, leave it selected.

o  Upsert Method - The upsert method defines how the upsert will function when executing the DML action. There are two modes

 The standard upsert method without a flag column - This mode will insert and update records in the destination  based on the Kay(s) selected in the column mappings. If the row exists, it gets updated, if it does not exist, the row is inserted.

 Row Flag Compare - This mode will update, insert, delete and reject rows based on the settings in the "Row Flag Column" drop down in the "Row Flag Column Settings" tab, the tab is only visible if this upsert method is selected.

 Destination DML Actions

  Insert

  Update

oRefresh Metadata - Refreshes the destination table's metadata.

  How to handle errors - Users have the ability to redirect erred rows to an error output. Select the "Redirect row to error output" option. Additionally, users have the options to ignore the errors but continue execution or to fail the component once an error is encountered.


Update Method

The update method tab allows you to control if updates occur on the selected destination table by giving you for options


Bulk Update - Bulk update is the default update method. In this method the destination is updated with whatever data is contained in the source as long as the keys selected in the columns mappings from the general tab match in the source and destination. It does not take into account whether data has changed or not.



Column Compare - Column compare can be used to compare whether data in the source matches what is in the destination. If the data does not match in the selected columns, the update occurs. Otherwise the update will not be performed. Keep in mind that this method will slow down the upsert process because of the comparison process.



Timestamp Compare - Timestamp compare will compare the value of a timestamp column from the source to a timestamp column in the destination



LastUpdate Compare -  Last update compare will compare a date column from the source to a date column in the destination. If the dates do not match, the row is updated.



Advanced Tab


  Command Timeout - Determines the time in seconds before a timeout occurs during the insert and updates to the destination table.

 Insert / Update Row Count Variable - To assign the number of rows inserted / update into the destination table during the upsert process a variable can be assigned to hold those values by choosing a variable from the drop down box for each count.

 Turn off internal transaction - Only use for performance reasons

 Use Merge Statement - Uses the MERGE statement instead of generating INSERT, UPDATE and DELETE statements to upsert the data into the destination table.

 Table Hint - When the "Use Merge Statement" property is not in use, define table hints in this field. For example: memory optimized tables require SNAPSHOT as a table hint.

(Note about upserting into memory optimized tables)

o  Table hints cannot be in use when "Turn off internal transactions" property is set to true.

o  Memory Optimized Tables do not allow use of the Merge Statement option.


Temp Table Definition - Users can define their own custom temporary table



Scripts - Users can write custom TSQL queries to be performed before or after each buffer.