Oracle Upsert Destination
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.
• For 64-bit Task Factory:
o 64-bit Oracle 12c Managed Data Access Client
• For 32-bit Task Factory:
o 32-bit Oracle 12c Managed Data Access Client
• Destination Information
o Destination Connection Manager - Choose an existing Connection Manager or create a new one. The only supported connections are ADO.NET Oracle.DataAccess Client connections
o Table Access Mode - The table access mode allows the user to define how the destination table name will be selected
■ Table - This mode allows the user to choose the table in the "Destination Table Name" drop down.
■ TableFromVariable - This mode will allow the user to choose a variable which contains the destination table name in the "Choose Table Variable" drop down.
o Destination Table Name - Available if "Table" was chosen as the access mode. Here you will choose the table name from the populated drop down menu.
o Choose Table Variable - Available if "TableFromVariable" was chose as the access mode. Here you will select a variable.
o Upsert Method - The upsert method defines how the upsert will function when executing the DML actions. 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 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 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.
o Destination DML Actions - Choose whether Upsert will insert or update records in the destination table.
o Refresh Metadata - Refreshes the destination table's metadata.
• Column Mappings
o Key - You must select at least one column to be used as the key.
o Input Column - the column name from the input. To select the destination column, simply click on the name of the input column in the grid and a drop down will appear.
o Destination Column - The column name from the destination in which the input column is mapped to.
• Update Method
o 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.
o 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 will not be performed.
o Last Update 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.
• Update "Last Modified" Column - Allows you to select a column to be updated with the date and time of when the row was updated.
• Command Timeout - The command timeout determines the number of seconds before a timeout occurs during the insert 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 box 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 will be inserting and updating rows.
• Rebuild temp table Indexes - Selecting this option will rebuild temp table indexes for each buffer.
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.