My AWS DMS task is successful, but no data is migrated from my Amazon S3 source endpoint. Why?

Last updated: 2019-09-30

My AWS Database Migration Service (AWS DMS) task is successful, but no data is migrated from my Amazon Simple Storage Service (Amazon S3) source endpoint. Why isn't my data migrating, and how do I troubleshoot this issue?

Short Description

The following are the most common scenarios in which an AWS DMS task is successful but no data is migrated:

  • The task status is Load complete, replication ongoing, but no data is loaded on the target.
  • The task status is Load complete, replication ongoing, but no table is in the Table statistics section.
  • The task status is Running and the table is created in the target endpoint, but no data is loaded. And you receive a No response body error in the replication log.

Resolution

1. The task status is Load complete, replication ongoing, but no data is loaded on the target

In this scenario, confirm that the Amazon S3 path defined for the source endpoint is correct. From the replication log, review the log entries that indicate that AWS DMS can't find the data files in the Amazon S3 path that is defined for the source endpoint. See the following example replication log entry:

Messages
[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 transfered 0.  (streamcomponent.c:3667)

In Amazon S3, the data file for the full load phase (data.csv) and the data file for on-going changes (change_data.csv) are stored as follows:

  • 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

For the Amazon S3 source endpoint to find the data files, there are three important fields: Bucket folder, CDC path, and Table structure. In the previous example, when creating the Amazon S3 source endpoint, dms-folder/sub-folder/ is the Bucket folder, and dms-cdc-path/dms-cdc-sub-path/ is the CDC path. See the following Table structure for this example:

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

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

When specifying your Endpoint configuration, consider the following:

  • The bucket folder is optional. If a bucket folder is specified, then the change data capture (CDC) path and table path (the TablePath field for a full load) must be in the same folder in Amazon S3. If the bucket folder isn't specified, then the TablePath and CDC path are directly under the S3 bucket. For example, AWS DMS reads data.csv from S3-bucket/dms_schema/dms_table/data.csv.
  • Bucket folders or CDC paths can be folders or sub folders, such as dms-folder or dms-folder/sub-folder.
  • The Bucket folder fields of the Amazon S3 source endpoint are anything between the S3 bucket name and the schema name of the table structure. For example, if the Amazon S3 path is s3://dms-test/dms-folder/my_schema/my_table/data.csv, in the example, dms-test is the bucket name, and my_schema is the database schema name. Using the same example, if you created an Amazon S3 source endpoint, then the Bucket folder field would be dms-folder.

If your AWS DMS task setting uses Amazon S3 as the source endpoint, then the schema (dms_schema in the example) and table (dms_table in the example) must be included in the table mapping to successfully migrate data to the target. For more information, see Source Data Types for Amazon S3.

If you use Drop tables on target as the table preparation mode for the AWS DMS task, then AWS DMS creates the target table dms_schema.dms_table, like in the following example:

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

If you use Do nothing or Truncate as the table preparation mode, then the table dms_schema.dms_table must be created manually with the same table structure that is specified in the Amazon S3 source endpoint.

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

If no table was created in the target endpoint when Drop tables on target was used for the table preparation mode, then the issue might be caused by the table structure that is specified for the Amazon S3 source endpoint.

Confirm that the Amazon S3 path for the source endpoint is correct, as described previously. Then, verify that your data type is supported by the Amazon S3 endpoint.

After confirming that the Amazon S3 path is correct and that your data type is supported, check the filter that is defined by the table mapping of your AWS DMS task to see if the filter is the cause of the missing tables. Be sure to include the table that is needed within the task table mapping and be sure that the table is defined in the table structure of the Amazon S3 source endpoint.

3. The task status is Running and the table is created in the target end, but no data is loaded

If AWS DMS can't get the content from the Amazon S3 path that is defined for the task, then you see errors similar to the following in the replication log:

Messages
[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 <wan-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)

Most often, this error occurs when the AWS Identity and Access Management (IAM) role for the Amazon S3 source endpoint doesn't have the correct permissions: s3:GetObject. To resolve this error, first confirm that the data file exists in the Amazon S3 path that is in the error message, and then confirm that the IAM user has permissions for s3:GetObject.


Did this article help you?

Anything we could improve?


Need more help?