Problem with update existing rows and insert new rows in SQL

[Venkata Jaggamma]

Hi,

I am using Dynamic RDBMS Stage to update existing records or insert new records into a target table. The Target database is on SQL Server.

I have observed that there are two statements in the DRS Stage. One is the update statement which tries to update the existing records. Second one is the insert statement which inserts new records.

However, the option does not seem to work when the update statement fails. New records are not getting inserted. When I tried to debug the job, I was able to find out that there is a problem at the DRS stage.

Can anyone let me know why it happens like this ?

[chulett]

Welcome. :D

First bit of advice would be to avoid the 'combo' Update actions. Best Practice is to evaluate inserts versus updates and use separate links for each action.

Secondly, it's not about the first action 'failing' per se. With an update, it should only execute the insert if the update does not update any records. If it actually fails, as in throws a database error, you are done.

Can you be more specific about the nature of your 'update statement failure'? Or the alleged problem you found 'at the DRS stage'?

[ray.wurlod]

When I tried to debug the job, I was able to find out that there is a problem at the DRS stage.

How?
And what is the precise nature of the problem?
Have you had your diagnosis verified by your support provider?