Invalid Column Name error when trying to update a row using Data Flow sink

Question

_{Tuesday, September 24, 2019 12:12 PM}

My actual use case is more complicated (and based on the SCD Type 2 template pipeline), but in attempting to debug my error I've created a new simplified data flow of the following format:

Data Source (data set is an Azure SQL Database table with a primary key column and multiple other NULLable columns)
Derived Column (creating a new column called `"NewRowStatus"` set to the literal string 'Historic')
Alter Row (single condition "Update If" set to true(), so all rows will update)
Sink to the same dataset we started with, mapping `"NewRowStatus"` to the "Row Status" database column, and with my primary key as the Key Column, allowing update.

Data Preview runs successfully for each step in the data flow, but when I run the entire data flow through a debug pipeline I get the following error: 

"error": { "code": 400, "message": "Job '[jobID] failed due to reason: DF-SYS-01 at Sink 'sink1': java.sql.BatchUpdateException: Invalid column name '{[My Key Column]}'.\njava.sql.BatchUpdateException: Invalid column name '{[My Key Column]}'... 

(square brackets are mine)

I've tried using different (non-key) columns to see if that made any difference, but so far I've not managed a single successful update. I'm not sure if I'm missing something really obvious, or if this is an issue with ADF still being in preview, but any thoughts would be appreciated.

All replies (3)

_{Wednesday, September 25, 2019 3:14 PM ✅Answered | 2 votes}

Figured this out, but in case anyone else stumbles across this with the same issue: ADF doesn't like key fields with spaces in them - changed the [Table Key] column to [TableKey] and all is well. No longer fits our design standards, but at least it works!

_{Wednesday, September 25, 2019 7:52 PM}

Hi jamiemacuk,

Thank you for sharing the resolution details. It might be beneficial to other community members reading this thread. 

If you have any suggestion/idea related to Azure Data Factory, please feel free to share your thoughts in Azure Data Factory feedback forum: https://feedback.azure.com/forums/270578-data-factory . All the feedback share here is monitored and reviewed by ADF engineering team.

Thank you

If a post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

_{Thursday, September 26, 2019 5:00 AM}

This is a UI bug which is being addressed. You should be able to use key columns with any special characters