Why didn't AWS DMS migrate data from my Amazon S3 source endpoint even though my AWS DMS task is successful?

Resolution

There are several reasons why an AWS DMS task is successful but doesn't migrate data. Complete the resolution for your use case.

The task status is "Load complete, replication ongoing", but the AWS DMS task isn't loading data on the target

Confirm that the S3 path that you defined for the source endpoint is correct. In the replication log, identify entries that show that AWS DMS can't find the data files in the S3 path.

Example replication log entry:

[SOURCE_UNLOAD ]I: Unload finished for table 'dms_schema'.'dms_table' (Id = 1). 0 rows sent. (streamcomponent.c:3396)
[TARGET_LOAD ]I: Load finished for table 'dms_schema'.'dms_table' (Id = 1). 0 rows received. 0 rows skipped. Volume transferred 0. (streamcomponent.c:3667)

Amazon S3 stores the data file for the full load phase (data.csv) and the data file for ongoing changes (change_data.csv) in the following format:

• S3-bucket/dms-folder/sub-folder/dms_schema/dms_table/data.csv
• S3-bucket/dms-folder/sub-folder/dms-cdc-path/dms-cdc-sub-path/change_data.csv

To find the data files, the S3 source endpoint uses the bucket folder, change data capture (CDC) path, and table structure fields.

In the preceding example file paths, dms-folder/sub-folder is the bucket folder. The CDC path that you enter when you create the S3 source endpoint is dms-cdc-path/dms-cdc-sub-path.

Example table structure that uses the example file path:

{
  "TableCount": 1,
  "Tables": [
    {
      "TableColumns": […],
      "TableColumnsTotal": "1",
      "TableName": "dms_table",
      "TableOwner": "dms_schema",
      "TablePath": "dms_schema/dms_table/"
    }
  ]
}

Important: Don't include the dms-folder/sub-folder bucket folder path in the TablePath of the table structure.

When you specify your Endpoint configuration, the bucket folder is optional. If you specify a bucket folder, then the CDC path and TablePath must be in the same folder in Amazon S3. If you don't specify the bucket folder, then the TablePath and CDC path are directly under the S3 bucket.

The bucket folder for the S3 source endpoint can be any folder directory between the bucket name and the schema name of the table structure. In the previous example table structure, the folder directory is dms-schema. If you don't have a hierarchy of folders under the bucket, then keep the field blank.

Note: Bucket folders and CDC paths can be individual folders or they can include subfolders, such as dms-folder or dms-folder/sub-folder.

If your DMS task setting uses an S3 source endpoint, then you must include the schema and table in the table mapping. The schema and table are required to successfully migrate data to the target. For more information, see Defining external tables for Amazon S3 as a source for AWS DMS.

If you use Drop tables on target as the table preparation mode for the task, then DMS creates the dms_schema.dms_table target table.

Example target table creation command:

CREATE TABLE 'dms_schema'.'dms_table' (...);

Note: Folder and object names in Amazon S3 are case-sensitive. Use the correct case when you specify the folder and object names in the S3 endpoint.

The task status is "Load complete, replication ongoing", but no table is in the Table statistics section

When you use Drop tables on target mode, AWS DMS might not create a table in the target endpoint. An incorrect S3 path or unsupported data type can cause this issue.

Confirm that the S3 path for the source endpoint is correct. Then, verify that your S3 endpoint supports the data type.

Check whether the filter that your DMS task's table mapping defined is causing the missing tables. Make sure that you defined the table that's required in the task table mapping in the table structure of the S3 source endpoint.

The task status is "Running" and the DMS task created the table in the target endpoint but didn't load data

If AWS DMS can't get the data from the S3 path, then use the replication log to find the errors. To get detailed logs, change the SOURCE_CAPTURE log level from Default to Detailed Debug.

Log example:

[SOURCE_CAPTURE ]E: No response body. Response code: 403 [1001730] (transfer_client.cpp:589)
[SOURCE_CAPTURE ]E: failed to download file </dms-folder/sub-folder/dms_schema/dms_table/data.csv> from bucket <dms-test> as </rdsdbdata/data/tasks/NKMBA237MEB4UFSRDF5ZAF3EZQ/bucketFolder/dms-folder/sub-folder/dms_schema/dms_table/data.csv>,
                status = 4 (FAILED) [1001730] (transfer_client.cpp:592)

You receive a "No response body" error in the replication log

The No response body error occurs when the AWS Identity and Access Management (IAM) role for the S3 source endpoint doesn't have the correct permissions. To resolve this issue, confirm that the data file exists in the S3 path that's in the error message. Then, confirm that the IAM user has permissions for s3:GetObject.

Note: If you activated versioning on the source bucket, then you must also grant s3:GetObjectVersion permission.

Related information

Using Amazon S3 as a source for AWS DMS