How to insert and update table in SSIS (Upsert or Merge)?

Source:http://pragmaticworks.com/forum/yaf_postsm46_How-to-insert-and-update-table-in-SSIS--Upsert-or-Merge.aspx#post46

Many times you have need to Insert new record or Update existing record in target table using SSIS. This can be done in 2 ways.

1. Combination of OLEDB Destination and OLEDB Command. This approach can be very slow if you have lots of updates. For large amount of records please check the next approach.
http://beingoyen.blogspo...e-instead-of-insert.html



2. Using TaskFactory Upsert Destination.
http://pragmaticworks.co...p_Upsert_Destination.htm

Upsert Destination Tutorial

http://pragmaticworks.co...rt_Destination_Video.htm

Upsert Destination (Batch Update Or Insert)

Helps synchronize two systems no matter the source or destination. Many companies struggle with the development of loading procedures that determine whether the row is new or to be updated. The Upsert Destination reads the rows coming into the data flow and detects if the row should be inserted or updated. This saves the developer a long development cycle and could save up to 90% on each execution of a package.

Key Features:

• Conditionally inserts or updates data into a table. Inserts are performed using bulk insert which is significantly faster compared to row-by-row approach. There are four methods in which data can be updated giving the user a great amount of control on whether data should be updated or not.
  1. Bulk update - Updates will be performed in the fastest amount of time because no data compare is performed. This is by far the fastest way to perform updates
  2. Column Compare - Updates will only be performed based on what columns the user selects to compare. If the data in the selected columns does not match, the update will be performed. Otherwise it will be ignored.
  3. DateTime Compare - Updates will only be performed based on a datetime column being compared from the source to a column in the destination.
  4. Timestamp Compare - Updates will only be performed based on a timestamp column being compared from the source to a column in the destination.
• Currently supports SQL 2005 and SQL 2008 as destination
• Easy to use User Interface