Skip navigation
Contact Us
780 Views 3 Replies Latest reply: Nov 20, 2012 6:49 PM by Felix Ho RSS
Matthew Walk Newbie 2 posts since
Nov 19, 2012
Currently Being Moderated

Nov 19, 2012 9:41 AM

Update records being written when nothing has changed.

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.

  • Felix Ho Expert 342 posts since
    Aug 23, 2011

    Hi,

     

    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

      • Felix Ho Expert 342 posts since
        Aug 23, 2011

        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

More Like This

  • Retrieving data ...

Bookmarked By (0)

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points