Hi, I have a Data Flow Task where in I bring oledb source table - TableA ( Membershipid, IsNewMembership) I then use a lookup transformation to lookup membershipid on another table ( TableB) and need to update a column in TableA - IsNewMembership ( based on the lookup match / non match) . I then use a 2 derived column transformation to replace the value of field IsNewMembership=="Y" in case of No Match Found and IsNewMembership=="N" in case of Match Found. I then use the Union All transformation to combine the values from the field IsNewMembership In the final step I bring in the oledbdestination -same TableA . The problem is neither the value of IsNewMembership gets updated and now I have double rows in my TableA ...... What is the best possible way to update a column(IsNewMembership ) in Table A looking up values in Table B - Please advice.... ThanksEVA05

Hi EVA05, No details provided on how you update, looks like you just do the insert, this is the issue, use a Script Transformation set as a destination and make the updates/additions in there but I can also tell you that if you are on a database that supports the SQL MERGE clause then you can simply solve this issue in just one hop: Execute SQL Task encompassing the T-SQL MERGE.Arthur My Blog

Hi ArthurZ , I tired with using MERGE statement ( on SQL 2008 R2) but it throws errors: Msg 10710, Level 15, State 1, Line 12 An action of type 'UPDATE' is not allowed in the 'WHEN NOT MATCHED' clause of a MERGE statement. Below is the code : MERGE TableA AS TARGET USING TableB AS SOURCE ON (TARGET.MembershipID = SOURCE.MembershipID) WHEN MATCHED AND (TARGET.QFormID = SOURCE.SourceInteractionKey) THEN UPDATE SET TARGET.IsNewMembershipID ='N' WHEN NOT MATCHED BY TARGET THEN UPDATE SET TARGET.IsNewMembershipID ='Y' Requirement : I have two Tables - TableA and TableB The two tables can be joined on MembershipID. TableB is my reference match. On joining on MembershipID from TableA and TableB if there is a match found I need to update field IsNewMembership='N' and if there is no match found I still need to update the field IsNewMembership='Y' on TableA What is the best optimal solution to achieve this - using TSQL or SSIS ? Please guide ..... ThanksEVA05

If the only thing you need to do is updating a column in Table A based on rows in Table B you don't need a data flow task. All you need it an Execute SQL Task with an Update statment which will first update all rows and set their IsNewMemberShip column to Y and then follow this up with another execute sql task with a Merge statement as mentioned by Arthur Z with the following script. MERGE INTO TableA A USING TableB B ON A.ID = B.ID WHEN matched THEN UPDATE SET A.IsNewMemberShip = 'N' However if you still want to do it in SSIS, would advise creating a temporary staging table and in the data flow task for all matching row make an insert into this table. Follow the data flow task with an execute sql task and write a simple update statment on table A using this temporary staging table. http://btsbee.wordpress.com/

Hi btsbee, I still get syntax error : Msg 156, Level 15, State 1, Line 11 Incorrect syntax near the keyword 'SET'. It pertains to the last statement. When I tried using UPDATE before SET it gives me the same error I mentioned : Msg 10710, Level 15, State 1, Line 10 An action of type 'UPDATE' is not allowed in the 'WHEN NOT MATCHED' clause of a MERGE statement. can you please guide me with the syntax.. MERGE INTO TableA A USING TableB B ON A.ID = B.ID WHEN matched THEN UPDATE SETA.IsNewMemberShip = 'N' Thanks, EVA05