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


Coming soon...

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. From the SSIS toolbox drag and drop three Trash Destination on the data flow designer surface.
    SSIS Trash Destination - Drag and Drop
  9. Now single click on the Upsert Destination, once you see blue arrow from source ... connect it to Upsert and New Records Trash Destination and for Red arrow connect it to Bad Records Trash Destination.
    SSIS Trash destination
  10. Thats all, you can run or execute task.
    SSIS Upsert destination - Execute

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.
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

Setting UI

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

See Also


Copyrights reserved. ZappySys LLC.