6

I am trying to move data from a oracle instance to postgres RDS using DMS. I am only doing a full load operation and I have disabled all the foreign keys on the target. I also made sure that the datatypes are not mismatched between columns for the same tables. I tried both 'Do Nothing' and 'Truncate' for the Target Table preparation mode and when I run the task, several tables are failing with below error messages:

[TARGET_LOAD ]E: Command failed to load data with exit error code 1, Command output: <truncated> [1020403] (csv_target.c:981)
[TARGET_LOAD ]E: Failed to wait for previous run [1020403] (csv_target.c:1578)
[TARGET_LOAD ]E: Failed to load data from csv file. [1020403] (odbc_endpoint_imp.c:5648)
[TARGET_LOAD ]E: Handling End of table 'public'.'SKEWED_VALUES' loading failed by subtask 6 thread 1 [1020403] (endpointshell.c:2416)

DMS doesn't give the correct error information and I am not able to understand what the above error messages mean.

When I use 'Drop tables on target' for the Target table preparation mode, it works but it creates the datatypes of the columns in a different way which I don't want.

Any help would be appreciated.

Hemanth
  • 705
  • 2
  • 16
  • 32

1 Answers1

1

To troubleshoot my case, I created a copy of the task that only loaded the one problem table, and upped all the logging severities to "Detailed debug". Then I was able to see this:

[TARGET_LOAD ]D: RetCode: SQL_SUCCESS_WITH_INFO SqlState: 42622 NativeError: -1 Message: NOTICE: identifier "diagnosticinterpretationrequestdata_diagnosticinterpretationcode" will be truncated to "diagnosticinterpretationrequestdata_diagnosticinterpretationcod" (ar_odbc_stmt.c:4720)

In the RDS logs for the target DB I found:

2021-10-11 14:30:36 UTC:...:[19259]:ERROR: invalid input syntax for integer: ""
2021-10-11 14:30:36 UTC:...:[19259]:CONTEXT: COPY diagnosticinterpretationrequest, line 1, column diagnosticinterpretationrequestdata_diagnosticinterpretationcod: ""
2021-10-11 14:30:36 UTC:...:[19259]:STATEMENT: COPY "myschema"."diagnosticinterpretationrequest" FROM STDIN WITH DELIMITER ',' CSV NULL 'attNULL' ESCAPE '\'

I found that if I added a table mapping rule to explicitly rename the column to truncate the name within Postgres's limit for identifier length, then things ran ok.

    {
        "rule-type": "transformation",
        "rule-id": "1",
        "rule-name": "1",
        "rule-target": "column",
        "object-locator": {
            "schema-name": "%",
            "table-name": "%",
            "column-name": "diagnosticinterpretationrequestdata_diagnosticinterpretationcode"
        },
        "rule-action": "rename",
        "value": "diagnosticinterpretationrequestdata_diagnosticinterpretationcod",
        "old-value": null
    },
Steve
  • 1,084
  • 11
  • 17