The Definitive Guide to Data Quality
Why do you remove the PK field from the schema?
Please show me the schema of your input file and the schema of your db table.
what information do you catch from sp_who?
is the job running well without call to sp_who?
any particularies on that table, contraints etc ?
Changing the key in your map isn't going to get the desired results as your database still thinks it has the same primary key. I think you'll need to use tMSSQLRow to do the update and specify your update query.
with a tMSSQLRow you will have to write by your own the sql update order, I don't think it's the wright way to do what you want
why won't you let insert or update key to id_cliente?
if you absolutely want to use check id num_pedido is present in pedido table I think you should separate "cliente" job and "pedido" job
unplug pedido output and transformation from your tmap
create another subjob (a copy) and had to the tmap a lookup with table pedido where join condition is row_a_1.id_pedido=pedido.num_pedido
set back pedido insert or update key to id_cliente
Why can't you just specify the correct primary key for pedido. If the record exists it will modify it, if it doesn't exist it will insert it.
If it doesn't come from the input file how do you expect to match it to your table? And trying to create it your way is always going to produce the null primary key in the output which will be locked after the first attempt at insert/update.
You wouldn't be able to insert without a proper primary key. You can use pedido as a lookup to match the num_pedido to and use the primary key from that for modify or you could use a tMSSQLRow for the update with an update where condition.