Unable to Perform Insert and Update

vardhan354 · Post by **vardhan354** » Wed Apr 12, 2006 11:12 am

Hi Ppl,

Im performing an insert and update to a table from a source table.Ever time when I run the Job, the job is not performing any insert and update,but instead it is performing truncating the table and inserting the data.When I viewed the log,I got the following message.

This update action 'insert or update' is not compatible with parameter array binding. Array Size property will be set to 1.

Im not able to understand what is stopping me to do Insert/Update even though I selected that Option.Please mail me your suggestion asap as Im running on a deadline.

Thanks a lot,

Bye,

V.V.G.

DSguru2B · Post by **DSguru2B** » Wed Apr 12, 2006 11:16 am

you are specifying the action as " insert new rows or update" or "update existing or insert new". With this, the array size has to be 1 and transaction size to 0. Fix that and you will get rid of that warning message.
About the truncate,
how do you know its truncating the table, is there data already present in the target? if there is and after the job finishes, theres no data then either in the after sql tab you must be providing a delete or truncate command.
Check if you have specified any after sql commands?

vardhan354 · Post by **vardhan354** » Wed Apr 12, 2006 11:34 am

Thanks D.S Guru!!! Yes, I can get rid of that message.

Here is the situation.The target table is loaded from two different sources 1 and 2 with insert and update action in the same job.Source 1 is empty.

Source1--->Target

Source2--->Target

When I run the job, I observed that there is no record of history i.e, Every time the target is loaded with fresh data.

Run1.

I ran the above job.I could see all the data in the target table.

Run2

I ran onlysource1 (No data)---> Target(has exisiting data before run).As we would assume that the data would be present in target after this run with no changes,surprisingly the table is showing no data.

Please throw u opinion.

Thanks,
V.V.G

DSguru2B · Post by **DSguru2B** » Wed Apr 12, 2006 11:46 am

did you analyze the before and after tab in the sql tab?

kcbland · Post by **kcbland** » Wed Apr 12, 2006 11:47 am

Which stage are you using, ODBC, Oracle OCI, DB2, Informix, Redbrick, etc?

vardhan354 · Post by **vardhan354** » Wed Apr 12, 2006 11:48 am

Yes,

I have seen the before and after tabs and there are no expressions in them

Looks wierd!!!

vardhan354 · Post by **vardhan354** » Wed Apr 12, 2006 11:49 am

Im connecting to MSSQL server thru DRS stage.

kcbland · Post by **kcbland** » Wed Apr 12, 2006 11:50 am

Are there triggers on the target table, or anything that would delete rows?

vardhan354 · Post by **vardhan354** » Wed Apr 12, 2006 11:51 am

How can I check if there are any triggers which are deleting.Im not aware!!!

kcbland · Post by **kcbland** » Wed Apr 12, 2006 11:58 am

Let me get this straight:

1. job #1 truncates the table and puts data into it
2. job #2 is supposed to insert or update existing data

After running job #1 you have data, but after running job #2 the table is emptied first and then only rows from job #2 are inserted?

Please verify the table is truly being truncated, because I have my suspicions. Please truncate the table, then insert a single row of dummy data. Now run job #2. Check to see if that dummy row is in the table. If it's not there, then you have something weird happening, meaning something is clearing the table.

If the dummy row is still there, then you have to evaluate why you thought the data was being truncated.

vardhan354 · Post by **vardhan354** » Wed Apr 12, 2006 12:09 pm

I thank you all for your suggestions.Since the job being already developed some one, I have no clue of its design.But I found that the table is getting truncated before the data is being loaded in to it and this action was mentioned at the source stage in the before tab.I apolozise for your valuable time in effort for answering me.

Thanks!!!!

ray.wurlod · Post by **ray.wurlod** » Wed Apr 12, 2006 4:53 pm

What we have here is a violation of the "law of least astonishment".

If you design jobs that perform "hidden" tasks - such as via before/after subroutines - place an annotation on the job canvas to alert future developers that invisible behaviour is happening in the job design.