Azure Data Factory: Copy Activity scenarios with 'Invalid column name' scenarios on Synapse Target
I've looked through the documentation and I'm still confused about I'm wondering if anyone has experience with I'm having trouble with I'm currently working on an Azure Data Factory pipeline that involves copying data from an Azure SQL Database to an Azure Synapse Analytics table. I've set up the Copy Data activity and configured the source and sink correctly, but when I run the pipeline, I encounter the following behavior: `Invalid column name 'XYZ'`. This behavior arises even though I have verified that the column exists in the target Synapse table. As part of troubleshooting, I've checked the following: 1. **Data Types**: The column types in the Azure SQL Database and Azure Synapse table match correctly. For example, the 'XYZ' column is an `NVARCHAR(50)` in both the source and sink. 2. **Mapping**: I've explicitly defined the column mapping in the Copy Data activity, ensuring that 'XYZ' from the source maps to 'XYZ' in the sink. 3. **Case Sensitivity**: I have confirmed that the casing of the column names is consistent between the source and target. However, the behavior continues. Here’s the relevant part of my pipeline configuration: ```json { "type": "Copy", "source": { "type": "SqlSource", "query": "SELECT XYZ FROM SourceTable" }, "sink": { "type": "SqlSink", "tableName": "TargetTable", "preCopyScript": "SET NOCOUNT ON;" }, "mapping": { "mappings": [ { "source": "XYZ", "sink": "XYZ" } ] } } ``` I've also attempted removing the column mapping altogether to see if ADF picks up the schema automatically, but I still get the same behavior. I even tried running a simple insert query directly on Synapse to rule out any issues with the target table, and it works fine. Could there be any underlying configuration issues with the Azure Synapse table that I might be missing, or is there a known limitation with Azure Data Factory that could lead to this behavior? Any insights would be greatly appreciated! Thanks for taking the time to read this! Could someone point me to the right documentation? The project is a REST API built with Json.