User configuration issue: Failure happened on 'Source' side.

Question

Hi

I am trying to copy Azure SQL database Prod Environment to Azure SQL database Sandbox Environment through ADF pipeline and getting below error during execution of copy data activity inside Foreach loop.

ADF Pipeline is failing during Copy data activity showing the below error, However I have validated preview data and under mapping import schemas all are working fine there is no invalid columns.

"errors": [

	{

		"Code": 11000,

		"Message": "Failure happened on 'Source' side. 'Type=Microsoft.Data.SqlClient.SqlException,Message=Invalid column name 'id'.\r\nInvalid column name 'name_space'.\r\nInvalid column name 'key'.\r\nInvalid column name 'value'.,Source=Framework Microsoft SqlClient Data Provider,'",

		"EventType": 0,

		"Category": 5,

		"Data": {

			"FailureInitiator": "Source"

		},

		"MsgId": null,

		"ExceptionType": null,

		"Source": null,

		"StackTrace": null,

		"InnerEventInfos": []

	}

If anyone come cross this kind of issue, please help me to fix the issue.

Thanks & Regards,

Kandan K

Answer 1

@Kandan-Azure - Glad to hear you fixed the initial issue with missing columns! Now, let's address the foreign key constraint errors encountered during the copy process.

The errors you are seeing related to foreign key constraints indicate that there are issues with the data integrity between the tables you are trying to copy.

Here are some steps to help you resolve these issues:

Check Data Integrity - Ensure that the data you are trying to insert into the child tables (e.g., po_line_detail) has corresponding entries in the parent tables (e.g., po_master). The foreign key constraints enforce that every value in the child table must have a corresponding value in the parent table.

Disable Foreign Key Constraints - You mentioned that you have added a pre-copy script to disable foreign key constraints. Make sure that this script is executed successfully before the copy operation. You can also add a post-copy script to re-enable the constraints after the data has been copied.

Data Order - If the tables have dependencies (i.e., foreign key relationships), ensure that you are copying the data in the correct order. Start with the parent tables and then move to the child tables. This ensures that all foreign key references are valid when inserting data.

Data Validation - Before running the copy activity, you can run queries to validate that all foreign key relationships are satisfied. For example, you can check for any orphaned records in the child tables that do not have corresponding records in the parent tables.

Error Handling - Implement error handling in your ADF pipeline to capture and log any errors that occur during the copy process. This can help you identify specific records that are causing issues.

Review Foreign Key Constraints - If you are still facing issues, review the foreign key constraints in your destination database to ensure they are set up correctly and that the data being copied adheres to these constraints.

By following these steps, you should be able to resolve the foreign key constraint issues and successfully copy the data from your source to the destination database.

Hope this helps. Do let us know if you have any further queries.