2

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

Improve this question
4
  • Where did you add the "derived column"? Can you give us any screenshots? Commented Jul 8, 2019 at 9:21
  • included the screenshot of the data flows Commented Jul 8, 2019 at 9:50
  • 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! Commented Jul 8, 2019 at 19:26
  • @MFox I solved this, please see my answer. Commented Jul 9, 2019 at 2:52

1 Answer 1

Reset to default
1

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Hi @Leon Yue, thanks a lot for your help, I managed to set up a working sample with the following limitations: 1. source column name (or from derived column name) must match schema of the sink 2. you need to map all columns from the source to the sink (or use the "Select" schema modifier to only select the columns we need in the sink). Therefore it seems there is no value yet to use the "mapping" in the sink as it doesn't work - probably a limitation in the preview version of data flows

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.