Support Ordinal Position Mismatches in cdc Replication - Prefer Column Name Instead.

Hello There,

Based on the requirement shared, I have tried the replicate the same at my end and was able to achieve the below.

• Created a database (employee) at the source instance with two tables X & Y.

  1. Create table tableX(createdby int, createddate datetime);
  2. Create table tableY(createddate datetime, createdby int);

  • Setup DMS RI & endpoints.

  • Configured the task with below mapping rule. * Selection rule: where schema name is like 'employee' and Source table name is like 'table%', include

    • Transformation rules: where schema name is like 'employee' and Source table name is like 'table%', rename
      • Action:Rename:FinalData
    • This would create a new table 'finaldata', in the target database 'employee'.

  • To test this further, I have executed below insert queries.

       * Insert into tableY values(CURRENT_TIME(),20);
           * Insert into tableY values(CURRENT_TIME(),20);
           * Insert into tableX values('12',CURRENT_TIME());
           * Insert into tableX values('12',CURRENT_TIME());
    

  • On the destination, the misplaced columns are handled, as indicated in the below result.

        select * from finaldata;
    

Hence, I would suggest you to try the above setup at your end and test it further.