DMS CDC & Full Load - Override default null-handling

Question

I have a replication task configured (only a single table) from Source MSSQL (v.14) to Target PostgreSQL (v.16) and nothing is being written to the target, aside from the DMS tables. When I view the logs, it is full of these types of messages, which means it is reading the CDC information and attempting to write the records.

**Message 2** (thousands): [2025-03-05T21:28:25 [**TARGET_LOAD **    ]D:  column value is NULL, use user defined NULL value attNULL  {operation:LOAD_DATA (33), tableName:Employee, schemaName:dbo, columnName:*ExampleColumnName*, connectionId:21103}  (csv_util.c:1368)]

I suspect the issue is the attNull value that DMS is attempting to use in place of NULL. How can I configure DMS to override this value?

I've tried putting this`"NullHandlingMode": "Passthrough" ` into the *TargetMetadata* JSON section, but that only seems to causes nulls in the SOURCE_CAPTURE

**Message**: [2025-03-05T23:04:59 [**SOURCE_CAPTURE**  ]D:  Going to prepare statement select  top 50000  	[Current LSN], 	[operation], 	[Context], 	[Transaction ID], 	[Transaction Name], 	[Begin Time], 	[End Time], 	[Flag Bits], 	[PartitionID], 	[Page ID], 	[Slot ID], 	[RowLog Contents 0], 	[Log Record], 	[RowLog Contents 1] -- After Image from ::fn_dblog (?, null) /*0 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL*/ where [Current LSN] collate SQL_Latin1_General_CP1_CI_AS > ? collate SQL_Latin1_General_CP1_CI_AS and (   (  [operation] in ('LOP_BEGIN_XACT','LOP_COMMIT_XACT','LOP_ABORT_XACT') )      or   (  [operation] in ('LOP_BEGIN_UPDATE', 'LOP_END_UPDATE') ) 	or   (  [operation] in ('LOP_INSERT_ROWS','LOP_DELETE_ROWS','LOP_MODIFY_ROW')      and      --[context]   in ('LCX_HEAP','LCX_CLUSTERED','LCX_MARK_AS_GHOST' /*,'LCX_TEXT_MIX'*/)      ( ( [context]   in ('LCX_HEAP','LCX_CLUSTERED','LCX_MARK_AS_GHOST') ) or ([context] = 'LCX_TEXT_MIX'))      and      [PartitionID] in (72057655137992704)   )   or   ([operation] = 'LOP_HOBT_DDL') )  (sqlserver_log_processor.c:6297)]

sam01m · Accepted Answer

It was working all along! The issue was that I created 'public.employee' in my target DB, but it was pushing the data to 'dbo.Employee'

I added a Schema Transformation to change 'dbo' to 'public' and the data ended up where I expected.

re:Post Agent · Answer

The issue you're experiencing with AWS Database Migration Service (DMS) not writing data to the target PostgreSQL database is likely related to how DMS is handling NULL values during the replication process.

To address this, you can modify the task settings to change how NULL values are handled. Instead of using the "NullHandlingMode" setting in the TargetMetadata section, you should adjust the "FullLoadSettings" and "ChangeProcessingTuning" sections of your task settings.

Here's what you can try:

1. In the task settings, locate or add the "FullLoadSettings" section and include the following:

```json
"FullLoadSettings": {
    "TargetTablePrepMode": "DO_NOTHING",
    "CreatePkAfterFullLoad": false,
    "StopTaskCachedChangesApplied": false,
    "StopTaskCachedChangesNotApplied": false,
    "MaxFullLoadSubTasks": 8,
    "TransactionConsistencyTimeout": 600,
    "CommitRate": 10000
}
```

2. In the "ChangeProcessingTuning" section, add or modify the following:

```json
"ChangeProcessingTuning": {
    "BatchApplyPreserveTransaction": true,
    "BatchApplyTimeoutMin": 1,
    "BatchApplyTimeoutMax": 30,
    "BatchApplyMemoryLimit": 500,
    "BatchSplitSize": 0,
    "MinTransactionSize": 1000,
    "CommitTimeout": 1,
    "MemoryLimitTotal": 1024,
    "MemoryKeepTime": 60,
    "StatementCacheSize": 50
}
```

3. Additionally, ensure that your table mapping includes the correct schema and table names, and that you're not accidentally excluding any columns.

If the issue persists after making these changes, you may need to investigate further by examining the source data, checking for any data type mismatches between the source and target, and ensuring that the DMS task has the necessary permissions to read from the source and write to the target.

Remember to stop and restart your DMS task after making these changes for them to take effect.

**Sources**  
[Issue with AWS DMS Full Load + CDC Task Not Reflecting Newly Created Tables in Target DB | AWS re:Post](https://repost.aws/questions/QUecWIOaAcTbWpF85GBwx9XA/issue-with-aws-dms-full-load-cdc-task-not-reflecting-newly-created-tables-in-target-db)  
[AWS DMS examples using Tools for PowerShell - AWS SDK Code Examples](https://docs.aws.amazon.com/code-library/latest/ug/powershell_4_database-migration-service_code_examples.html)

DMS CDC & Full Load - Override default null-handling

Add your answer

Relevant content