SSIS Upsert Destination
 PreviousNext

Upsert destination can be used to synchronize data from source to target. It supports Bulk Upsert (insert if does not exist else update) and bulk delete. The Upsert destination is used no synchronize data between source and destination in few clicks.

Download SSIS PowerPack

Content

Video Tutorial


Step-By-Step

In this tutorial we will learn component for Upsert operation (PostgreSQL, Amazon Redshift and SQL Server Database). This component can perform High performance Bulk Upsert (Insert + Update) in a single operation. It uses Key based Lookup. You can also do Bulk Delete or Bulk Update.
  1. Firstly, You need to Download and Install SSIS ZappySys PowerPack.
  2. Once you finished first step, Open Visual Studio and Create New SSIS Package Project.
  3. Now, Drag and Drop SSIS Data Flow Task from SSIS Toolbox.
    SSIS Data Flow Task - Drag and Drop
  4. Double click on the Data Flow task to see Data Flow designer surface.
  5. Here, In Visual Studio, drag and drop the OLE DB Source and Upsert Destination (Insert, Update, Delete) in the design surface and join the component with the blue arrow.
    SSIS Upsert Destination - Drag and Drop
  6. Now click on to create PostgreSQL Connection, Amazon Redshift Connection and OLE DB Connection.
  7. Do Upsert for PostgreSQL, Amazon Redshift and SQL Server Database.

How to create OLE DB Connection.

  1. Lets, create first OLE DB Connection.
  2. Now, Right click on Connection Managers Panel to Create OLEDB Connection, so you can use Source and Context Menu will appear, Select New OLEDB Connection from the Context Menu.
    SSIS OLEDB - Connection
  3. Now, click on New Button for create Connection.
    SSIS OLEDB - Connection
  4. Let's Configure Connection Manager, just Follow steps one by one as we have created.
    SSIS OLEDB - Connection
  5. Click on Test Connection to see correct configure it.
  6. Click on OK button to save connection configure setting UI.

How to insert or update using Upsert Destination in PostgreSql (Bulk Upsert, Update, Delete).

  1. In this section we are going to do Upsert into PostgreSQL from SQL Server table.
  2. Double click on OLE DB Source for configure it.
  3. Lets Configure in Connection Manager, just follow below image steps.
    SSIS OLEDB Connection Configure
  4. Click OK to Save OLEDB Source Editor UI Settings.
  5. Now, double click on Upsert Destination for configure it.
  6. Set Action to Upsert => (insert if not matching in target else update). Select Target Connection and Target Table. Check on Insert and Update. Click on Map All to Mappings all columns and check on Only Primary Key columns.
    SSIS SQL Upsert option
  7. Click on OK to save Upsert Destination settings UI.
  8. Thats all, you can run or execute task.
    SSIS Upsert destination - Execute

How to track New Inserted/Updated/Bad Record. (Optional Steps)

  1. After configuring the upsert destination, you can track newly inserted, updated, or bad rows by following the steps below.
  2. From the SSIS toolbox drag and drop three Trash Destination on the data flow designer surface.
    SSIS Trash Destination - Drag and Drop
  3. Now single click on the Upsert Destination, once you see blue arrow from source ... connect it to Upsert and New/Updated Records Trash Destination and for Red arrow connect it to Bad Records Trash Destination.
    SSIS Trash destination

How to insert or update using Upsert Destination in Amazon Redshift (Bulk Upsert, Update, Delete).

  1. In this section we are going to do Upsert into Amazon Redshift from SQL Server table.
  2. Double click on OLE DB Source for configure it.
  3. Lets Configure in Connection Manager, just follow below image steps.
    SSIS OLEDB Connection Configure
  4. Click OK to Save OLEDB Source Editor UI Settings.
  5. Now, double click on Upsert Destination for configure it.
  6. Set Action to Upsert => (insert if not matching in target else update). Select Target Connection and Target Table. Check on Insert and Update. Click on Map All to Mappings all columns and check on Only Primary Key columns.
    SSIS SQL Upsert option
  7. Click on OK to save Upsert Destination settings UI.
  8. Thats all, you can run or execute task.
    SSIS Upsert destination - Execute

How to insert or update using Upsert Destination in SQL Server Table (Bulk Upsert, Update, Delete).

  1. In this section we are going to do Upsert into SQL Server to SQL Server table.
  2. Double click on OLE DB Source for configure it.
  3. Lets Configure in Connection Manager, just follow below image steps.
    SSIS OLEDB Connection Configure
  4. Click OK to Save OLEDB Source Editor UI Settings.
  5. Now, double click on Upsert Destination for configure it.
  6. Set Action to Upsert => (insert if not matching in target else update). Select Target Connection and Target Table (You can create New Table if not exist using [New] Button to perform Insert). Check on Insert and Update. Click on Map All to Mappings all columns and check on Only Primary Key columns.
    SSIS SQL Upsert option
  7. Here, we have Advanced Options Tab for more settings. Here you can Duplicate data Handling.
    SSIS Upsert destination advanced options (Pre / Post SQL Command, Staging Table name)
  8. Click on OK to save Upsert Destination settings UI.
  9. Thats all, you can run or execute task.
    SSIS Upsert destination - Execute

How to Upsert with Delete using Upsert Destination.

  1. Double click on Upsert Destination for configure it.
  2. Set Action to Sync => Upsert + Delete (Removes rows from target if not found in source). Select Connection and Target Table.

    Check on Delete from target if not found in source. Click on Map All to Mappings all columns and check on Only Primary Key columns.
    SSIS upsert synchronization
  3. Thats all, Click on OK to save Upsert Destination settings UI.

How to Only Mass Update data using Upsert Destination.

  1. Double click on Upsert Destination for configure it.
  2. Set Action Bulk Update => based on matching records on target. Select Connection and Target Table. Click on Map All to Mappings all columns and check on Only Primary Key columns.
    SSIS Bulk Update rows in SQL Table
  3. Thats all, Click on OK to save Upsert Destination settings UI.

How to Only Mass Delete data using Upsert Destination.

  1. Double click on Upsert Destination for configure it.
  2. Set Action Bulk Delete => based on matching records on target. Select Connection and Target Table. Click on Map All to Mappings all columns and check on Only Primary Key columns.
    SSIS Bulk delete rows in SQL Table
  3. Thats all, Click on OK to save Upsert Destination settings UI.

Properties

Property Name Description
LoggingMode LoggingMode determines how much information is logged during Package Execution. Set Logging mode to Debugging for maximum log.

Available Options (Use numeric value listed in bracket if you have to define expression on this property (for dynamic behavior).

Option Description
Normal [0] Normal
Medium [1] Medium
Detailed [2] Detailed
Debugging [3] Debugging
PrefixTimestamp When you enable this property it will prefix timestamp before Log messages.
DoNotCreateOutputs Enable / Disable Response Output. Checking this option can remove unwanted warnings about unused Columns and many times speedup performance so if output is not needed set this option to True so its removed.
TableName Target table name where you like insert, update or delete records
BatchSize Commit batch size (0=Commit once all source rows are consumed). You can Change Batch Size to commit in smaller chunks too if error handling needed.
Action Action you like to perform (e.g. Upsert, Insert, Update, Delete or Sync)

Available Options (Use numeric value listed in bracket if you have to define expression on this property (for dynamic behavior).

Option Description
Upsert [0] Upsert => Insert if not matching in target else Update
Sync [1] Sync => Upsert + Delete (Removes rows from target if not found in source)
Delete [2] Bulk Delete => based on matching records on target
Update [3] Bulk Update => based on matching records on target
EnableInsert INSERT source rows to target if they don't exist in target table (Only for Sync or Upsert Action)
EnableUpdate UPDATE source rows to target if they exist in target table (Only for Sync or Upsert Action)
EnableDelete DELETE target rows if missing in source (Only for Sync or Upsert Action)
CustomTempTableName User defined Temp table to hold source result before final merge. This table is truncated before each data load. Unlike auto temp table, custom temp table is not dropped after process is finished.
TempPostExecuteCommand SQL command you like to execute after source data is loaded into temp table. For example you can use this option to issue Custom SQL to delete duplicate rows from Staging Table right before final merge. This command is executed before issuing final merge on target table. Along with variable placeholders, you can also use special placeholders [%target_table%], [%temp_table%] and [%target_keys%]
PreExecuteCommand SQL command you like to execute before starting load in target table (e.g. DROP INDEX on target). Along with variable placeholders, you can also use special placeholders [%target_table%], [%temp_table%] and [%target_keys%]
PostExecuteCommand SQL command you like to execute at the end once target table is update (e.g. REBUILD or CREATE INDEX on target table). Along with variable placeholders, you can also use special placeholders [%target_table%], [%temp_table%] and [%target_keys%]
DuplicateRemovalMode Specifies what to do if you encounter duplicate records in source data. You can use first row in the group or keep last row or throw error.

Available Options (Use numeric value listed in bracket if you have to define expression on this property (for dynamic behavior).

Option Description
None [0] None
KeepFirst [1] KeepFirst
KeepLast [2] KeepLast
DuplicateOrderBy Specifies field name based on Duplicate record is sorted. You can enter one or more columns based on you like sorting (e.g. col1 asc, col2 desc, upper(col3) desc). If you do not specify anything then default sort is based on Key field(s). By default UI will warn you if you enter custom value. If you do not wish to be prompted on the UI for custom expression then you can enter /*NO-PROMPT*/ anywhere in the value (e.g. LastUpdated DESC /*NO-PROMPT*/)
RowCompareMode Defines how to check for modified row for Update Action. Default is check by Key match (Update all rows which exists on target by matching Keys from source and target).

Available Options (Use numeric value listed in bracket if you have to define expression on this property (for dynamic behavior).

Option Description
Key [0] Compare Key (Default)
KeyAndColumns [1] Compare Key + Other Column(s)
EnableCreatedOn Enables writing row create date/time to the selected target column if row is inserted.
EnableLastUpdatedOn Enables writing row update date/time to the selected target column if row is updated.
ColumnForCreatedOn Indicates target table column name which you like to set for row create action date/time. After row insert this column will be updated automatically with the current server date/time (only if you set column name here). If you map this column from source input then it takes precedence.
ColumnForLastUpdatedOn Indicates target table column name which you like to set for row update action date/time. After row update this column will be updated automatically with the current date/time (only if you set column name here). If you map this column from source input then it takes precedence.
CustomTableHints Custom hints for merge if supported by database engine. E.g. in SQL Server you can issue WITH (TABLOCK) hint if you dont want to lock target table during merge operation allowing other processes to access it while merge is performed. Leaving this value blank will use system default hints (i.e. WITH (HOLDLOCK) for MS SQL and blank for other engines). If you do not wish to use any hints then enter {none}

Setting UI

SSIS Upsert Destination - Setting UI
SSIS Upsert Destination - Setting UI
SSIS Upsert Destination - Setting UI

See Also

Copyrights reserved. ZappySys LLC.