Hi, First of all, let me tell you I have not used SSIS Merge transformation before. My Scenario Table1 : Existing data (20 million records) Table2 : New data (can be between 10000 to 2 million records monthly) So every month, I get new data in Table2. I need to 1) Insert the New data from Table2 to Table1. 2) Update some of the rows from the new data from Table2 based on PK(Composite of some columns) So every month, I need to update existing rows in Table1 and Insert New rows from Table2. Can I use SSIS Merge for this? If yes, how can I do it? If no, which other method can I use? Thanks in advance, Mandar

Refer to below thread on comparision of three options of doing this http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/5afc3806-4681-4b6a-aed9-bdf56127ef49/

you can use Merge Join Transformation for this , you should have two different oledb sources, for each of tables, sort their data with ORDER BY clause in oledb source sql statement, then set IsSorted property in advanced editor as true. then join these two oledb source with a merge join transform with joining key. set join as left outer join, and note that table2 should be left source. then you can use a conditional split to find out match rows and no match rows and redirect them to oledb command for update or oledb destination for insert. also you can use Lookup transform for this UPSERT ( Update/Insert ) operation. set an oledb source for table2, and add a lookup transform, set table1 as lookup table. then redirect match output to an OLEDB Command for update purpose and redirect no match output to an OLEDB Destination for insert purpose. and also you can use T-sql MERGE function for this ( I strongly recommend this solution if your tables are in sql server DB ). this will perform much faster than previous solutions. more information about merge structure and samples are here: http://msdn.microsoft.com/en-us/library/bb510625.aspxhttp://www.rad.pasfu.com

Hi Reza, As this is SQL Server 2005 (which I forgot to mention above, my bad) , I cannot use MERGE statement in T-SQL. But I will try to use the other options in SSIS and let you know... Thanks, Mandar

Hi Reza, As this is SQL Server 2005 (which I forgot to mention above, my bad) , I cannot use MERGE statement in T-SQL. But I will try to use the other options in SSIS and let you know... I would like to add that it is more performant to send the update rows to a staging table and then perform a set-based update with TSQL, instead of using the OLE DB command. This command updates your table on a row per row basis, which is ridiculously slow on large datasets.MCTS, MCITP - Please mark posts as answered where appropriate.