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

Kandan-Azure 0

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

Ganesh Gurram 2,305 Reputation points Microsoft Vendor

2024-12-17T18:45:48.9533333+00:00

Hello @Kandan-Azure

Thanks for the question and using MS Q&A platform.

The error message "Invalid column name" indicates that the copy data activity is encountering columns in the source database that don't exist in the destination database.

Here are some steps to troubleshoot and fix this issue:

Verify Column Names - Double-check the source database schema to ensure that the columns 'id', 'name_space', 'key', and 'value' exist and are correctly spelled.

Check Mapping - Ensure that the mapping in your ADF pipeline is correctly configured to match the source schema. If the column names have changed or if there are any discrepancies, update the mapping accordingly.

Preview Data - Even though you mentioned that you validated the preview data, it’s worth revisiting to ensure that the data being pulled from the source is indeed what you expect.

Review Source Table - If you have recently altered the source table (e.g., renamed columns or changed the schema), make sure that the ADF pipeline reflects those changes.

Error Handling - Implement error handling in your pipeline to log more detailed information about the failure, which may help in diagnosing the issue further.

Refer to this similar thread: Copy to avtivity giving an error Error Code 11000

References: Troubleshoot connector and format issues in mapping data flows in Azure Data Factory

Troubleshoot the Azure Synapse Analytics, Azure SQL Database, SQL Server, Azure SQL Managed Instance, and Amazon RDS for SQL Server connectors in Data Factory in Microsoft Fabric

Hope this helps. Do let us know if you have any further queries.
Kandan-Azure 0 Reputation points

2024-12-18T17:44:25.35+00:00

Hi @Anonymous ,

Thank you for sharing the troubleshooting steps and able to fix the fix and ADF pipeline is started running and phase running time some of the tables are not copy showed the errors like Foreign key constraint, however I have added pre-copy script to disable the Foreign Key constraint before to the Copydata activity.

FOREIGN KEY disable:

ALTER TABLE @{concat(item().table_schema, '.', item().table_name)} NOCHECK CONSTRAINT ALL;

Errors :

1)

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_po_notes_po_master". The conflict occurred in database "CCSDB-temp-sandbox", table "dbo.po_master", column 'po_id'

2)

User configuration issue

Failure happened on 'Sink' side. ErrorCode=SqlOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed. Please search error to get more details.,Source=Microsoft.DataTransfer.Connectors.MSSQL,''Type=Microsoft.Data.SqlClient.SqlException,Message=The INSERT statement conflicted with the FOREIGN KEY constraint "FK__po_verify_detail__408F9238". The conflict occurred in database "CCSDB-temp-sandbox", table "dbo.po_line_detail".

The statement has been terminated.,Source=Framework Microsoft SqlClient Data Provider,'

Thanks & Regardsm

Kandan K
Ganesh Gurram 2,305 Reputation points Microsoft Vendor

2024-12-19T18:32:40.3966667+00:00

@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.
Ganesh Gurram 2,305 Reputation points Microsoft Vendor

2024-12-20T16:03:19.62+00:00

@Kandan-Azure - We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Ganesh Gurram 2,305 Reputation points Microsoft Vendor

2024-12-23T03:14:07.7266667+00:00

@Kandan-Azure - Following up to see if the above suggestion was helpful. And, if you have any further query do let us know.

Share via

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

Your answer