Does a SSIS Execute SQL Task handle a MERGE statement?

Question

I'm trying to execute a MERGE statement via a Execute SQL Task, as outlined in this article:

http://technet.microsoft.com/en-us/library/cc280522.aspx

My MERGE statement is quite simple (see below). I can execute the MERGE statement with no trouble in SSMS, but when I put it into an Execute SQL Task, it fails with this error: "Incorrect syntax near the keyword 'AS'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

The statement will not parse in an Execute SQL Task container, but obviously does (since it runs correctly) in SSMS. Are there any tricks or syntax considerations I need to know about when using a MERGE statement in an Execute SQL Task?

MERGE HospitalDailyWastage AS TGT
USING NightlyHospitalWastage AS SRC
ON (
        TGT.HospitalID = SRC.HospitalID AND
        TGT.WastageDate = SRC.WastageDate AND
        TGT.ProductGroupID = SRC.ProductGroupID AND
        TGT.DataTypeID = SRC.DataTypeID AND
        TGT.ServiceLineID = SRC.ServiceLineID
    )
WHEN NOT MATCHED BY TARGET
    THEN INSERT (   
            HospitalID,
            WastageDate,
            ProductGroupID,
            DataTypeID,
            ServiceLineID,
            OPos,
            ONeg,
            APos,
            ANeg,
            BPos,
            BNeg,
            ABPos,
            ABNeg,
            Auto,
            LastUpdated,
            UpdatedBy
        ) VALUES (
            SRC.HospitalID,
            SRC.WastageDate,
            SRC.ProductGroupID,
            SRC.DataTypeID,
            SRC.ServiceLineID,
            SRC.OPos,
            SRC.ONeg,
            SRC.APos,
            SRC.ANeg,
            SRC.BPos,
            SRC.BNeg,
            SRC.ABPos,
            SRC.ABNeg,
            SRC.Auto,
            getdate(),
            'system'            
        )   
WHEN MATCHED 
    THEN UPDATE SET TGT.OPos = SRC.OPos,
                    TGT.ONeg = SRC.ONeg,
                    TGT.APos = SRC.APos,
                    TGT.ANeg = SRC.ANeg,
                    TGT.BPos = SRC.BPos,
                    TGT.BNeg = SRC.BNeg,
                    TGT.ABPos = SRC.ABPos,
                    TGT.ABNeg = SRC.ABNeg,
                    TGT.Auto = SRC.Auto,
                    TGT.LastUpdated = getdate(),
                    TGT.UpdatedBy = 'system';

Answer 1

First are you sure that you have a the right connection to the same server you were able to run the statement on in SSMS? Certainly it would error out if the server you are connecting to is set in compatibility mode. Check all the settings of your exec SQl task, perhaps it isn;t the query but the way the task is set up. Can you run a select hitting the same tables from the exec sql as it is currently set-up?

I've never tried to run merge in an Exec SQl (we are in compatibility mode so I don't get to play with the new syntax), but have you tried this?

Make the sql into a stored proc. Make sure you can execute the proc onthe same server the connection is for.

then exec the proc in the exec SQl task. Does that work?

Answer 2

I assume the T-SQL is correct. Have you tried to put semicolon as a first character of yout T-SQL? I had similar problem with CTE and putting ';' before 'WITH' helped.