Task Factory Upsert Destination

Upsert Destination

Upsert Destination

Destination IconDestination Description
Task Factory Upsert Destination IconThe Upsert Destination is used to update and insert data into a SQL table. Use Upsert Destination to insert new rows, and perform updates at the same time.

General 

Task Factory Upsert Destination General

OptionDescription
Destination Connection ManagerSelect or create the connection manager that upserts data into the destination.
Table Access ModeTable access mode allows the user to define how the destination table name is selected. There are two modes :

  • Table - This mode allows you to choose the table in the Destination Table Name drop down.
  • TableFromVariable - This mode allows you to choose a variable that contains the destination table name in the Choose Table Variable drop down, only visible when TableFromVariable is selected.
Destination Table Name / Choose Table VariableChoose either the destination table or the table variable.
Enable Identity InsertThis 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 inserts data into the identity column, unselect this option. If the source contains at least some rows that insert into the identity column, leave it selected.
Upsert MethodThe upsert method defines how the upsert functions when executing the DML action. There are two modes:

  • The standard upsert method without a flag column - This mode inserts and updates records in the destination based on the Key(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 updates, inserts, deletes and rejects 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
Refresh MetadataRefreshes the destination table's metadata.
How to handle errorsUsers have the ability to redirect erred rows to an error output. Select the Redirect row to error output option. Additionally, users have the option 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 four options:

Update MethodDescriptionImage
Bulk UpdateBulk 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.Task Factory Upsert Destination Update Method Bulk Update
Column CompareColumn 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 does not be perform. Keep in mind that this method slows down the upsert process because of the comparison process.Task Factory Upsert Destination Update Method Column Compare
Timestamp CompareTimestamp Compare compares the value of a timestamp column from the source to a timestamp column in the destination.Task Factory Upsert Destination Update Method Timestamp Compare
LastUpdate CompareLastUpdate Compare compares a date column from the source to a date column in the destination. If the dates do not match, the row updates.Task Factory Upsert Destination Update Method LastUpdate Compare

Advanced Tab

Task Factory Upsert Destination Advanced

OptionDescription
Command TimeoutDetermines the time in seconds before a timeout occurs during the insert and updates to the destination table.
Insert / Update Row Count VariableTo 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 transactionOnly use for performance reasons.
Use Merge StatementUses the MERGE statement instead of generating INSERT, UPDATE and DELETE statements to upsert the data into the destination table.
Table HintWhen 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 : 

  • Table hints cannot be in use when Turn off internal transactions property is set to true.
  • Memory Optimized Tables do not allow use of the Merge Statement option.

Temp Table Definition

Task Factory Upsert Destination Temp Table Definition

Users can define their own custom temporary table.

Scripts 

Task Factory Upsert Destination Scripts

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

Oracle Upsert Destination

Oracle Upsert Destination

Destination IconDestination Description
Task Factory Oracle Upsert Destination IconThe Oracle Upsert Destination is used to conditionally insert and update data into an Oracle table within SSIS. Oracle Upsert Destination uses temporary tables. If during execution there if a failure, the removal of the temporary table could be effected and be left on the server.

Requirements

For 64-bit Task Factory:For 32-bit Task Factory:

General 

Task Factory Oracle Upsert Destination

Destination Information 

OptionDescription
Destination Connection ManagerChoose an existing Connection Manager or create a new one.

Note:  The only supported connections are ADO.NET Oracle.DataAccess Client connections.
Table Access ModeThe table access mode allows the user to define how the destination table name is  selected.

  • Table - This mode allows the user to choose the table in the Destination Table Name drop down.
  • TableFromVariable - This mode allows the user to choose a variable that contains the destination table name in the Choose Table Variable drop down.
 Destination Table NameAvailable if Table was chosen as the access mode. Here you choose the table name from the populated drop down menu.
Choose Table VariableAvailable if TableFromVariable was selected as the access mode. Here you select a variable.
Upsert MethodThe upsert method defines how the upsert functions when executing the DML actions. There are two modes:

  • The standard Upsert method without a flag column - This mode inserts and updates records in the destination based on the key(s) selected in the column mappings. If the row exists, it gets updated, if it does not exist, the row gets inserted.
  • Row Flag Compare - This mode updates, inserts, deletes, and rejects rows based on the settings in the Row Flag Column drop down in the Row Flag Column Settings tab.
Destination DML ActionsChoose whether Upsert inserts or updates records in the destination table.
Refresh MetadataRefreshes the destination table's metadata.

Column Mappings

OptionDescription
KeyYou must select at least one column to be used as the key.
Input ColumnThe column name from the input. To select the destination column, select the name of the input column in the grid to display a drop-down menu.
Destination ColumnThe column name from the destination that the input column is mapped to.

Update Method

OptionDescriptionImage
Bulk UpdateBulk 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.Task Factory Oracle Upsert Destination Update Method Bulk Update
Column CompareColumn Compare can be used to compare whether data in the source matches what is in the destination. If the data does not match, the update occurs. Otherwise the update is not performed.Task Factory Oracle Upsert Destination Update Method Column Compare
Last Update CompareLastUpdate Compare Compares a date column from the source to a date column in the destination. If the dates do not match, the row is updated.Task Factory Oracle Upsert Destination Update Method Last Update Compare

Update Last Modified Column

Allows you to select a column to be updated with the date and time of when the row was updated.

Advanced Tab

Task Factory Oracle Upsert Destination Advanced

OptionDescription
Command TimeoutThe Command Timeout determines the number of seconds before a timeout occurs during the inserts and updates to the destination table.
Insert Row Count Variable / Update Row Count VariableTo assign the number of rows inserted or updated 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 menu for each count.
Turn off internal transactionsThis option is used to turn off the internal transaction that upsert creates when executing the inserts and updates. This is useful if another component is going to retrieve data from the destination table at the same time as the upsert inserting and updating rows.
Rebuild temp table IndexesSelecting this option rebuilds temp table indexes for each buffer.

Scripts

Task Factory Oracle Upsert Destination Scripts

Users can write custom TSQL queries to be performed before or after each buffer. To enable the Scripts tab, users must turn off internal transactions in the Advanced Tab.

Additional Videos