Change Columns Ordinal Position

Question

Is it possible to change the ordinal position of table column(s) via a Data Migration Task? If a Table in a Source Endpoint has columns out of order with regard to the Target Endpoint, can the order be changed without manual coding?

The existing migration is as follows.

- Hop 1 (data migration task): On-Prem-DB ---> S3:One
- Hop 2 (Lambda copy): S3:One ---> S3:Two
- Hop 3 (data migration task): S3:Two ---> RDS-DB

NOTE: These are "Full-Load, Ongoing" replication tasks with three hops. The S3 buckets contain the standard "dbo" folders for full load and "cdc-files" folder for ongoing. The On-Prem is using SQL Server CDC to log changes. Also, I can't change the table names in the target as this is a many to one replication and 80% of the databases contain identical tables. There are just a few older source databases with column ordinality differences.

I can ignore columns, add columns, however the order in which the columns are placed in "cdc-files" and "full-load" seem to be dictated by the Source table structure and can't be changed. Hoping for confirmation on this.

Accepted Answer

You are correct.  DMS task  cannot handle change in the ordinal position of target table column(s)  in the given scenario.

However , if we  migrate directly from source database to target database , DMS can migrate to target table  (manually created) with different column order  using  "Do nothing" or  "Truncate"  for  "target table preparation mode"  in task settings  *[link](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Tasks.CustomizingTasks.TaskSettings.FullLoad.html)*  .

Change Columns Ordinal Position

Relevant content