DMS with version 3.6.1, headers for source tables aren't replicated to the target using transformations rule

Yes, this is a known issue with DMS version 3.6.1 specifically when using SQL Server as source and Redshift as target. As you've noted, these columns work correctly in DMS 3.5.4.

Recommended solutions:

• Downgrade to DMS 3.5.4 (as you've verified this version works correctly)
• Wait for a patch/fix from AWS for version 3.6.1
• If you must use 3.6.1, you can manually create these columns in your target tables

You can also:

• Open a support case with AWS to get the latest status on this issue
• Monitor the AWS DMS release notes for updates addressing this problem

Hi Sandeep,

I was able to create a full load and CDC task migrating data from SQL Server to Redshift using DMS 3.6.1 and using table headers expressions. In my task, I added transformation rules for converting schema, tables, and columns to lower case and also added transformation rules to add columns with expressions AR_H_TIMESTAMP, AR_H_STREAM_POSITION, AR_H_COMMIT_TIMESTAMP, AR_H_OPERATION and AR_H_CHANGE_SEQ. Both full load and cdc worked fine.

Would you be able to share the table mapping JSON that you are using? Please also share the error you are facing. I will try to reproduce the error in my lab.

Sample table used in my tests:

create table sqltst (c1 int primary key, c2 int);

Sample table mapping rule:

 {
            "rule-type": "transformation",
            "rule-id": "222",
            "rule-name": "222",
            "rule-target": "column",
            "object-locator": {
                "schema-name": "%",
                "table-name": "%"
            },
            "rule-action": "add-column",
            "value": "ar_h_timestamp",
            "expression": "$AR_H_TIMESTAMP",
            "data-type": {
                "type": "string",
                "length": 50
            }
        },
        {
            "rule-type": "transformation",
            "rule-id": "22",
            "rule-name": "22",
            "rule-target": "column",
            "object-locator": {
                "schema-name": "%",
                "table-name": "%"
            },
            "rule-action": "add-column",
            "value": "transact_id",
            "expression": "$AR_H_STREAM_POSITION",
            "data-type": {
                "type": "string",
                "length": 50
            }
        },

Is there any update on this as we are also experiencing the same problem but our source is MySQL and target is MSSQL. After upgrading from 3.5.3 to 3.6.1, we saw influx of error below:

Failed to init column calculation expression '$AR_H_TIMESTAMP,$AR_H_COMMIT_TIMESTAMP,$AR_H_STREAM_POSITION,
$AR_H_OPERATION,$AR_H_CHANGE_SEQ,$AR_H_USER'  

We already raised a support case, but I would like to know if there are any workaround without downgrading to the previous version.