ADF Data Flows: invalid column name

2
votes

I want to create a simple azure data factory process to read a file (csv) from blob storage and write it to an azure sql database using data flows.

the source dataset has a column with the name "myTime" of type "string".

I added a "derived column" to add a new column with the name "customTime" with the expression "currentTimestamp()"

finally, in the sql sink, I mapped "customTime" to my DateTime field on the database.

enter image description here

If I preview the data in data flows, everything looks alright, I can see both fields (myTime, customTime). When I debug the pipeline, I get the following exception:

Activity myActivity failed: DF-SYS-01 at Sink 'sqlsink': java.sql.BatchUpdateException: Invalid column name 'myTime'

Any idea why the sql sink is linked to "myTime" and not "customTime"? I don't see any reference to "myTime" except that this is part of the input schema.

Thank you very much and best regards Michael

1
azureazure-data-factory
Where did you add the "derived column"? Can you give us any screenshots? – Leon Yue
included the screenshot of the data flows – MFox
Can you please paste in your DSL script? It's the code-behind for the UI graph. Go to your data flow UI then click Code > Plan. I'd like to see your mapping. Thanks! – Mark Kromer MSFT
@MFox I solved this, please see my answer. – Leon Yue

1 Answers

1
votes

This error is caused by the column mapping. You create the new column "customTime", but in your table the column name is "myTime".

What I found that is the csv file column name and schema must be same with the Sink table, or it will gives an error when debugging, this my error message:

enter image description here

Solution 1:: choose the "Recreate table" in Sink settings. But this will change your table schema: myTime to customTime.

enter image description here

Solution 2: Add the same column name with Sink table in Derived Column's Settings.

enter image description here

I test with the same opreation with and it worked succeeded.

I read a file (csv) from blob storage and write it to my Azure SQL database using data flows. I create a the Sink table with same schema.

This My csv file:

enter image description here

My data flow: enter image description here

Step 1: Source settings: enter image description here

Step 2: Derived Column's Settings enter image description here

Step 3: Sink setting: enter image description here

Running succeeded: enter image description here

enter image description here

Hope this helps.