The INSERT command in the WHEN NOT MATCHED clause of a MERGE command is not working as expected...

MERGE dbo.MyTargetTable AS Target
USING (SELECT * FROM dbo.MySourceTable) AS Source
ON (Target.colx = Source.colx)
WHEN MATCHED
 THEN UPDATE SET
 Target.[ColA] = Source.[ColA],
 Target.[ColB] = Source.[ColB],

WHEN NOT MATCHED
 THEN INSERT
 VALUES
 (
 Source.[ColA],
 'Some String Value',
 )
OUTPUT $action, Inserted.*, Deleted.*;

As you can see, I'm just trying to set ColB to a string 'Some String Value' when the record gets inserted into the target table.   This works in SSMS but not in the Execute SQL Task.  Do I need to do anything special to set up the MERGE command to work in SSIS? Thanks.

Hi,

MERGE statement works with SSIS Execute SQL Task without any additional configuration. I have written many with SSIS. Your code should work, however I notice that an additional comma just after 'Some String Value', hope it is not the issue you face.

Can you please share the error you get with SSIS? Do you expect an output from this statement and want to capture it to another container? If so, have you set "Resultset" property to something else other than "none'?

---------------------------

Dinesh Priyankara
http://dinesql.blogspot.com/