Verifying Successful Update Operation and WHERE Clause Validation.

[MernaAbdallah]

Hello,

I am facing an issue verifying whether an UPDATE operation has been successfully executed, both when using EXECUTE CUSTOM QUERY and Entities.

Problem Details:

1-Using EXECUTE CUSTOM QUERY:

I Tried SELECT @@ROWCOUNT approach and when I execute an UPDATE statement followed by SELECT @@ROWCOUNT in the same custom query task, only the UPDATE runs, and no row count is returned.

If I run SELECT @@ROWCOUNT as a separate query, it always returns the affected rows as 1 regardless of whether the WHERE condition was met. This seems to be the default behavior.

2-Using Entities:

Entities do not provide a direct way to confirm whether the UPDATE was applied successfully.
There is no built-in mechanism to ensure that the WHERE clause in the UPDATE query is validated and that the operation was executed as expected.

I need a reliable way to verify that the WHERE clause in my UPDATE query is properly validated and confirm that the update operation has been successfully applied in both scenarios.

I want to achieve this by using the connectorOutputPayload directly, without creating a new database connector just to select the updated task by its ID.

[phertzog]

Hello,

If you have the option, you can try to create a stored procedure to execute the update. This way you should be able to control the return of the stored procedure and return the number of updated rows.

Of course this doesn't work if you don't have the rights to add stored procedures in the database