CODEWITHSUNDEEP
azure-data-factory
jamiemacuk
jamiemacuk

Reputation: 31

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

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.

Upvotes: 0

Views: 1700

Answers (1)

jamiemacuk
jamiemacuk

Reputation: 31

So in trying to get some non-sensitive screenshots, I created an even simpler new data flow, and that one worked fine. After a bit of digging, it seems like ADF doesn't like it when your key field in the sink has a space in it. Having amended my Dimension table's key column to not include a space, and re-run my original data flow, everything is now working as expected.

Upvotes: 3

Related Questions