Documentation forTask Factory

Task Factory Upsert Destination

Upsert Destination

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

General 

Task Factory Upsert Destination General

Option Description
Destination Connection Manager Select or create the connection manager that upserts data into the destination.
Table Access Mode Table access mode allows you 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 Variable Choose either the destination table or the table variable.
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 inserts data into the identity column, deselect this option. If the source contains at least some rows that insert into the identity column, leave it selected.
Upsert Method The 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 Metadata Refreshes the destination table's metadata.
How to handle errors Select the Redirect row to error output option to redirect rows with errors to an error output. Additionally, you have the option to ignore the errors and 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 Method Description
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 is not performed. Keep in mind that this method slows down the upsert process because of the comparison process.

Note:  Column Compare uses GetDate() to generate the timestamp, and updates the rows per buffer/batch. For each buffer/batch, the row updates to the value of GetDate() at the time the insert/update/merge statement executes.
Timestamp Compare Timestamp Compare compares the value of a timestamp column from the source to a timestamp column in the destination.
LastUpdate Compare LastUpdate Compare compares a date column from the source to a date column in the destination. If the dates do not match, the row updates.

Advanced Tab

Task Factory Upsert Destination Advanced

Option Description
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:  When 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

You can define your own custom temporary table.

Scripts 

Task Factory Upsert Destination Scripts

You can write custom T-SQL queries to be performed before or after each buffer.

Note:  When redirecting errors to the error output:

  • If a failure occurs during the regular upserting of rows through bulk copy, the component begins using a row by row mode. When this occurs, each row from the source is inserted/updated for the current buffer of rows; usually around 10,000. If any row fails to insert/update then that row is pushed to the error output.
  • If a failure occurs due to duplicate rows (based on the keys selected in the column mappings) in a single buffer, the first duplicated row is inserted, with all subsequent rows processed as updates to the first row. When this happens, you rarely see rows being sent to the error output unless a different error occurs on the individual insert/update. Non-sorted rows can present data quality issues.

Samples

See the taskfactory-samples repository on GitHub for the following Upsert Destination samples:

  • 1_SetupUpsertDemoDBs.dtsx - Use to set up the Upsert samples
  • UpsertDestination_BasicSetup.dtsx - A simple upsert configuration
  • UpsertDestination_ColumnCompareWithAuditVariables.dtsx - Upsert configured with comparison columns and auditing variables to show the number of records inserted / updates
  • UpsertDestination_ErrorOutput.dtsx - Upsert configured with error output
  • UpsertDestination_InsertOnly.dtsx - Upsert configured for insert only
  • UpsertDestination_UpdateOnly.dtsx - Upsert configured for update only

Oracle Upsert Destination

Destination Icon Destination Description
Task Factory Oracle Upsert Destination Icon The 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.

Oracle Requirements

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

Oracle General 

Task Factory Oracle Upsert Destination

Oracle Destination Information 

Option Description
Destination Connection Manager Choose an existing connection manager or create a new one.

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

  • 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.
 Destination Table Name Available if Table was chosen as the access mode. Here you choose the table name from the populated drop down menu.
Choose Table Variable Available if TableFromVariable was selected as the access mode. Here you select a variable.
Upsert Method The 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 Actions Choose whether Upsert inserts or updates records in the destination table.
Refresh Metadata Refreshes the destination table's metadata.

Oracle Column Mappings

Option Description
Key You must select at least one column to be used as the key.
Input Column The 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 Column The column name from the destination that the input column is mapped to.

Oracle Update Method

Option Description
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, the update occurs. Otherwise the update is not performed.
Last Update Compare LastUpdate 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.

Oracle Update Last Modified Column

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

Oracle Advanced Tab

Task Factory Oracle Upsert Destination Advanced

Option Description
Command Timeout The 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 Variable To 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 transactions This 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 Indexes Selecting this option rebuilds temp table indexes for each buffer.

Oracle Scripts

Task Factory Oracle Upsert Destination Scripts

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

Oracle Additional Videos

Support Articles

How to use the Row Flag Compare option in the Upsert Destination

See the above article for a step-by-step guide.

Upsert Destination Hangs

Article demonstrates the most common way to resolve the Upsert Destination hanging during execution.

Oracle Upsert Destination – Could not delete temporary table

If an error occurs in Oracle while the Oracle Upsert Destination is writing to the database, the component will attempt to rollback all changes and remove temporary tables that were in use. This can cause an Illegal ROLLBACK attempt to occur if Oracle believes the component is attempting to write to the temporary table while the rollback attempt is made. Typically, the error will present itself in SSIS as:

[TF Oracle Upset Destination [29]] Error: Oracle.DataAccess.Client.OracleException ORA-00600: internal error code, arguments: [4513], [130], [1]... 

or

[TF Oracle Upsert Destination [29]] Error: Oracle.DataAccess.Client.OracleException ORA-24795: Illegal ROLLBACK attempt made at...

Oracle Upsert Destination Error

[TF Oracle Upsert Destination [26]] Error: System.Exception: Could not create temporary destination table: Could not add the unique index or primary key index to the destination table because no unique indexes or primary key indexes are defined for 'Tablename'