Subject: Issue with Column Mapping in Data Pipeline After Database Migration

Question

Description: We have migrated our database from IT to IT1. After updating the pipeline to use the new IT1 database linked service, we are encountering the following error:

Failure happened on 'Sink' side. ErrorCode=UserErrorInvalidColumnMappingColumnNotFound,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Column name 'CREATION_DATE' cannot be found in either source data or column mapping.,Source=Microsoft.DataTransfer.DataContracts,'

Details:

• Source Database: Oracle
• Sink Database: SQL Server (IT1)
• Column in Question: CREATION_DATE

Actions Taken:

1. Verified that the column 'CREATION_DATE' exists in both the source and sink tables.
2. Checked and updated the column mapping in the pipeline configuration.
3. Confirmed the linked service for IT1 is correctly configured.
4. Refreshed metadata and tested the pipeline with sample data.

Request: Could you please assist in identifying the cause of the column mapping issue and guide us on resolving it? Any additional logs or diagnostic information would also be appreciated.

Thankyou.

Answer 1

While you mentioned that you've confirmed the existence of the CREATION_DATE column in both the Oracle source and the SQL Server sink, it's crucial to ensure that the column names match exactly, including case sensitivity, spaces, or any special characters. Even a small discrepancy in the column name could lead to the error you're seeing.

If the data is being transformed as it moves from the Oracle source to the SQL Server sink, there might be an issue where the CREATION_DATE column is being renamed, removed, or altered in the process. Double-check any transformation steps in your pipeline to ensure that the column is correctly mapped and passed through to the sink.

After the database migration, it's important to refresh the metadata in Azure Data Factory to ensure that it recognizes the current schema of the tables in both databases. You mentioned refreshing metadata, but it might be helpful to revalidate the linked service and datasets to ensure that the schema is up-to-date and correctly recognized.

Ensure that the column mapping between the source and sink is explicitly defined and that it correctly maps CREATION_DATE from the source to the sink. Sometimes, if the mapping is left to be inferred, minor differences between the schemas can cause issues. Explicitly setting the mapping can help avoid such problems.

Differences in data types between the source and sink databases can sometimes cause unexpected issues during data transfer. Additionally, any constraints on the CREATION_DATE column, such as NOT NULL constraints or default values, could also impact the pipeline if they are not correctly handled.

If these assumptions don't directly resolve the issue, I recommend reviewing any available logs or diagnostics inADF for more detailed error messages.