DMS task target load error: Invalid input for table (SQL Server 2017)

Question

I have two DMS tasks which load to the same table (both source and target are SQL Server 2017) with different filters - the task that succeeds has this filter:

```
            "filters": [
                {
                    "filter-type": "source",
                    "column-name": "id",
                    "filter-conditions": [
                        {
                            "filter-operator": "ste",
                            "value": "391471128"
                        }
                    ]
                }
            ]
```

the task that fails has this filter:

```
            "filters": [
                {
                    "filter-type": "source",
                    "column-name": "id",
                    "filter-conditions": [
                        {
                            "filter-operator": "gte",
                            "value": "391471129"
                        }
                    ]
                }
            ]
```

Getting error

```
2023-04-19T16:57:59 [TARGET_LOAD     ]E:  **Invalid input for table** 'dbo'.'blablabla' in line number 1000 [1022510]  (sqlserver_endpoint_imp.c:2683)

```

Tried reloading into that table again in the task table stats UI but got the same error in the task log.

Couldn't find a shred of info on this error in Google nor in repost.aws.

Answer

Hello There,

I understand that you have a DMS task that is failing with mentioned filter with the error message [TARGET_LOAD     ]E:  **Invalid input for table** 'dbo'.'blablabla' in line number 1000 [1022510]  (sqlserver_endpoint_imp.c:2683)

First of all, This kind of error happens on a SQL Server target generally as result of primary key violations. You can review that from your side as well. There is also a part of documentation here which covers this kind of error.

[+] : https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Troubleshooting.html#CHAP_Troubleshooting.General.PKErrors

As mentioned, The above error indicates that DMS failed to apply records to the target database due to constraint violation issues so here we have to make sure target tables are empty before reloading the tables. If the target tables were not truncated before reloading the tables, these errors are expected.

Initially, you can verify and note down the migration type and what value is set for TargetTablePrepMode and then you can follow the set of action below if the issue can be mitigated.

- Modify the DMS task TargetTablePrepMode to "TRUNCATE_BEFORE_LOAD" if there is any other value set. 
- Try to set the recovery model to 'FULL' or 'BULK LOGGED'. 
- Check the tables that should have atleast one primary key or unique key. If not, please add them in order to run validation on the table.
- Enable the control table task settings to check if the tables are suspended. 
- Restart your DMS task.

Moreover, if you still face the issues you can enable debug logging to see more detailed information on these errors and troubleshoot further. Please refer to document [1] and [2] for more information on logging. Kindly note that, enabling detailed debugging causes increased storage usage. Please ensure to disable logging after troubleshooting/relevant log generation.

That being said, since this issue might need deeper investigation you can always open a support case with AWS Support Engineering Team. Certain non-public information might be required such as your DMS task details and information regarding your source and target endpoints hence you can open a support case with AWS using the support link and the issue can be investigated for further troubleshooting.

[1] : How do I set up detailed debug logging for my AWS DMS task? - https://repost.aws/knowledge-center/dms-debug-logging

[2] : Logging task settings - https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Tasks.CustomizingTasks.TaskSettings.Logging.html

DMS task target load error: Invalid input for table (SQL Server 2017)

Relevant content