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.

AWS-User-6238408 · 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

Contenido relevante