SSIS Execute SQL Task, Rows affected on MERGE Statement (Oracle & SQL Server)

Question

I have an SSIS package where I need to execute a MERGE statement with an Execute SQL Task. I need to do this both on an Oracle DB and a SQL Server 2012 DB. My question is, how can I get the number of rows affected as a ResultSet so I can save it on a variable in the SSIS package? I don't need to know if updated or inserted, and I don't want to create a procedure on Oracle because the MERGE is dynamic and calculated in the package through expressions in variables. I just want to get the value of the rows affected back.

P.S.: If I can't use the Execute SQL Task, can i at lease use a Script to run the MERGE? The problem is, from what I read, that ExecuteNonQuery() on Oracle does not return the rows affected with a MERGE. And I couldn't find any information for SQL Server. Can anyone help?

Thanks in advance.

UPDATE: I just checked a script with ExecuteNonQuery() for SQL Server, and it did return the number or rows affected. It would however be better if I could avoid the script and use the Execute SQL Task, but how do I get the rows affected as a ResultSet?

You can definitely do it with a Script task. Don't know about Oracle, but look into using ExecuteScalar(). — Tab Alleman
Wouldn't ExecuteScalar() only work if a table is returned and retrieves the first row of the first column? The number of rows affected is not returned in a table, is it? — stackoverflowthebest

rvphx rvphx · Accepted Answer · 2015-02-02T20:55:31

On SQL Server you can use the OUTPUT clause with the merge statement. Here's something for you to read up. And for Oracle, read this: PLSQL: Get number of records updated vs inserted when a merge statement is used

https://www.simple-talk.com/sql/learn-sql-server/implementing-the-output-clause-in-sql-server-2008/

SSIS Execute SQL Task, Rows affected on MERGE Statement (Oracle & SQL Server)

1 Answers