We have a mapping that sources data from a Microsoft SQL Server machine and pushes it to an Oracle machine. There is one particular column that is causing update records to be written even when nothing has changed. The Microsoft SQL Server source column is defined as decimal(9,2) and the Oracle destination column is number(9,2).
Interestingly, there are approximately 180,000 rows that get pushed from the source to the target, yet only 4,000 of the records (the same 4,000 each time) have duplicated update records written. When debugging the mapping, the two values look identical (Example source = 3.85 destination = 3.85), yet the duplicated update records still get written.
We've tried manually converting the source column to FLOAT in the mapping, that still didn't do the trick.
If we select the "Ignore in Comparison" bit for that one column, no update records get written. If we unselect that bit, the duplicate update records get written again.
Should we be converting the source and/or target columns to something else in order to get the lookup to work correctly? Do you have any other suggesstions? Any advice is appreciated.
For checking the column comparsion, you should check for the whole pipeline.
Make sure the following:
1.) No transformation after lookup to target
2.) No transformation from source to before lookup
3.) Make sure source DB extract the value are the same
4.) Make sure target DB and inserted value are the same
We have tried with no transformations on the source and target and verified that the source and target values "look" the same using the debugger. It's still writing duplicate updates. Any other suggestions?
If you confirm that all, then we can focus on the lookup transformation.
There are three thing inside impact the result.
1.) Input port - Is your input port equal to the source pipeline value?
You can only check for the configuration such as datatype, precision, scale...
2.) comparison port - Is your input port compare with the correct output port??
It's also about configuration
3.) The lookup cache - Is your lookup cache equal to the target table???
- Check if you unclick 'reuse lookup cache', if you clicked, it use the old cache file which may be wrong
- Delete the cache and rebuild, it can make sure the cache equal to the table