DSXchange

Hi,

I have a strange problem going on with a job design.
Job has 2 ORA stages. Source ORA stage is executing a SELECT on source table and pulling some data for a couple of columns. Target ORA stage is executing an Upsert - If seq_no (key column) exists then execute an Update else execute an Insert.

problem is that - when target table is empty, the insert is executed and records pulled from source table are inserted to target and there is no warning or error message on job and counts are correct. But data is inconsistent., in the sense, for some records, randomly, instead of the column values nulls are inserted to target table.

Is it due to some partitioning or job design flaw?

To be more clear, let me explain with example
Can anyone suggest things to change in this design to make it work right?

thanks in advance

Does the job literally have two stages in it? ORA --> ORA?

Just a guess and as something to try, add the following environment variable to your job:
APT_DISABLE_COMBINATION=True

Yes, the job has only two stages ORA -> ORA

I will surely try with that environment variable. since this kind of behaviour happens randomly to random records, i will do a couple runs to check if this works.

thanks

Unfortunately, adding that environment variable didnt help. the first two test runs still had the same behaviour

Thanks

Can you make a copy of the job and table and make the column non-nullable, then put a reject link to the upsert and see if you get some more information explaining this behaviour?

ArndW,
I did as you suggested and set one of the columns (colB) to not-nullable and ran the test jobs and nothing was captured in the rejects and the column (colB) was populated correctly for all records.
however i still have inconsistent data for the other columns which are still nllable columns (colA and colC)

But my question is this - what about columns that are genuinely nullable and where source has nulls that are valid values as well.

But i like it that we are making some improvement . Thank you

I expected rejects, not correct records... What about putting a transform stage between source and target, putting in a second output link to a peek stage with a constraint of only sending records where the value of a column is null. That way you could see if the problem is happening on the read or at the write.