Upsert Destination (Batch Update or Insert)

Upsert Destination - Step 1

General Tab

Upsert Destination - Step 2

Destination Information

Destination Connection Manager
The destination connection manager is the manager that will be used to upsert data into the destination. Currently the only supported connections are ADO.NET SQL Client connections.
You can choose to use an existing ADO.NET SQL Client connection or <Create New Connection>

Upsert Destination - Step 3

When adding the new connection you must select a connection manager provider that is of type "System.Data.SqlClient".

Upsert Destination - Step 4

You can also add a new connection by clicking the "New" button.
When adding your new connection you must select .NET Providers \ SqlClient Data Provider.

Upsert Destination - Step 5

Destination Table Name
After the connection is selected in the "Destination Connection Manager" the "Destination Table Name" drop down will be populated with the available tables from the connection manager. Select the table to Upsert the data into.

Upsert Destination - Step 6

After a table is selected the Column Mappings section will be enabled.

Destination DML Actions / Enable Identity Insert

You can choose whether Upsert will insert or update records in the destination table.

To turn off either the Insert or Update action, uncheck the box. This will prevent Upsert from inserting or updating records into your destination table.

When the 'Update' action is checked, the 'Update' tab will show up at the top of the UI.

The 'Enable Identity Insert' is automatically selected when Upsert detects if the destination table contains an identity column. If the selected source for Upsert doesn't contain a column that will insert data into the identity column, uncheck this selection. If the source at least will contain at least some rows that will be inserted into the identity column, leave this selected checked.

After a table is selected the Column Mappings section will be enabled.

Destination DML Actions / Enable Identity Insert

You can choose whether Upsert will insert or update records in the destination table.

To turn off either the Insert or Update action, uncheck the box. This will prevent Upsert from inserting or updating records into your destination table.

When the 'Update' action is checked, the 'Update' tab will show up at the top of the UI.

The 'Enable Identity Insert' is automatically selected when Upsert detects if the destination table contains an identity column. If the selected source for Upsert doesn't contain a column that will insert data into the identity column, uncheck this selection. If the source at least will contain at least some rows that will be inserted into the identity column, leave this selected checked.

Upsert Destination - Step 7

Column Mappings

The column mappings section allows you to map the columns from the source to the columns in the destination. If the same column name exists in the source and destination then the mappings will automatically be mapped.

Upsert Destination - Step 8

Grid Columns

The grid contains three columns.
Key - You must select at least on column to be used as the key. Select the column is the primary key in the destination table. If the destination table doesn't have a primary key (which is bad), select the column that contains unique values for each row. Key columns are highlighted in green.
Input Column - The column name from the input
Destination Column - The column name from the destination in which the input column is mapped to. To select the destination column, simply click on the name of the destination column in the grid and a drop down will appear.

If you would like to not insert data into a destination column choose '<ignore>'.

Grid Columns

The grid contains three columns.
Key - You must select at least on column to be used as the key. Select the column is the primary key in the destination table. If the destination table doesn't have a primary key (which is bad), select the column that contains unique values for each row. Key columns are highlighted in green.
Input Column - The column name from the input
Destination Column - The column name from the destination in which the input column is mapped to. To select the destination column, simply click on the name of the destination column in the grid and a drop down will appear.

If you would like to not insert data into a destination column choose '<ignore>'.

Upsert Destination - Step 9

Update Method Tab

Upsert Destination - Step 10

Update Method

The update method tab allow you to control if updates occur on the selected destination table by giving you four options.

Upsert Destination - Step 11

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 doesn't match what's in the destination. If the data doesn't 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.

Select the columns (only columns mapped in the general tab are available) that you would like to be compared in the grid by checking the checkbox next to the source column name.

Upsert Destination - Step 13

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.

Upsert Destination - Step 14

Update 'Last Modified' Column

The Update 'Last Modified' Column allows you to select a column to be updated with the date and time of when the row was updated.

Upsert Destination - Step 15

Advanced Tab

Upsert Destination - Step 16

Command Timeout

The comment timeout determines the number of seconds before a timeout occurs during the insert and updates to the destination table. Leave this at 0 unless you truly need to change the number of seconds before the command times out.

Insert Row Count Variable / Update Row Count Variable

To assign the number of rows inserted / 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 be retrieve data from the destination table at the same time as the Upsert will be inserting and updating rows.

Command Timeout

The comment timeout determines the number of seconds before a timeout occurs during the insert and updates to the destination table. Leave this at 0 unless you truly need to change the number of seconds before the command times out.

Insert Row Count Variable / Update Row Count Variable

To assign the number of rows inserted / 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 be retrieve data from the destination table at the same time as the Upsert will be inserting and updating rows.

Feature Highlights