Azure data factory data flow is failing while loading data from csv(without header) to SQL MI

Question

Azure data factory data flow is failing while loading data from csv(without header) to SQL MI

Junaib v m 6

Dear Team,

Azure data factory data flow is failing when loading data from csv(without header) to SQL MI.

Error:
"Job failed due to reason: at Sink 'EntityAddressrelationSTG': shaded.msdataflow.com.microsoft.sqlserver.jdbc.SQLServerException: Invalid column name 'VALUE'."

Subashri Vasudevan 11,226 Reputation points

2022-07-21T13:23:46.927+00:00

Hi @Junaib v m ,

Seems like there is no column named VALUE in the sink table.

Did you check in the sink dataset schema if it has any column named VALUE?

chance could be, while the dataset was created , the table had a column named VALUE, and later on it was dropped. But the schema remained unrefreshed.

Please check in the schema of the sink data set and do a refresh once.

Kindly revert if you need more assistance.

Thanks.
Junaib v m 6 Reputation points

2022-07-21T14:04:36.49+00:00

Hi @Subashri Vasudevan ,

"Value" column is not present in source and sink.
The input file is a csv that is without header, SO the ADF will give the header like column1....column2....
Iam not sure about the "VALUE" column.
Subashri Vasudevan 11,226 Reputation points

2022-07-21T14:23:54.177+00:00

May be the first row as header is selected and it has a value called VALUE?

are you able to preview the file in data flow ?
Junaib v m 6 Reputation points

2022-07-21T14:48:00.267+00:00

"When creating the source data set i have unchecked the "First Row Header" option.
Iam able to preview the data file.
Subashri Vasudevan 11,226 Reputation points

2022-07-21T14:51:13.987+00:00

What about sink preview? Is it rendering fine or it errors out?

Please post screenshot of sink settings ,dataset and data preview to investigate further.
Junaib v m 6 Reputation points

2022-07-21T15:27:49.793+00:00
Junaib v m 6 Reputation points

2022-07-21T15:42:21.69+00:00
Subashri Vasudevan 11,226 Reputation points

2022-07-21T16:17:47.173+00:00

Request you to share the schema of the sink data set.
Junaib v m 6 Reputation points

2022-07-21T16:45:08.033+00:00
Subashri Vasudevan 11,226 Reputation points

2022-07-22T05:43:44.767+00:00

Hi,

What are you doing in the pre SQL script ? Can you please check if there is a reference to VALUE column in your pre SQL script
Junaib v m 6 Reputation points

2022-07-22T06:30:41.203+00:00

Pre SQL and Post SQL are empty
Subashri Vasudevan 11,226 Reputation points

2022-07-22T06:54:05.163+00:00

It's surprising.

Pls try to delete the script text area shown below and close the browser and try debugging again. Could be a caching issue. But let's see.
Junaib v m 6 Reputation points

2022-07-22T08:12:49.237+00:00

Tried the above workaround, But getting same error.
Subashri Vasudevan 11,226 Reputation points

2022-07-22T10:28:32.487+00:00

@ShaikMaheer-MSFT request you to advise further on the issue.

2 answers

Your answer

Subashri Vasudevan 11,226 Reputation points

2022-07-21T13:23:46.927+00:00

Hi @Junaib v m ,

Seems like there is no column named VALUE in the sink table.

Did you check in the sink dataset schema if it has any column named VALUE?

chance could be, while the dataset was created , the table had a column named VALUE, and later on it was dropped. But the schema remained unrefreshed.

Please check in the schema of the sink data set and do a refresh once.

Kindly revert if you need more assistance.

Thanks.
Junaib v m 6 Reputation points

2022-07-21T14:04:36.49+00:00

Hi @Subashri Vasudevan ,

"Value" column is not present in source and sink.
The input file is a csv that is without header, SO the ADF will give the header like column1....column2....
Iam not sure about the "VALUE" column.
Subashri Vasudevan 11,226 Reputation points

2022-07-21T14:23:54.177+00:00

May be the first row as header is selected and it has a value called VALUE?

are you able to preview the file in data flow ?
Junaib v m 6 Reputation points

2022-07-21T14:48:00.267+00:00

"When creating the source data set i have unchecked the "First Row Header" option.
Iam able to preview the data file.
Subashri Vasudevan 11,226 Reputation points

2022-07-21T14:51:13.987+00:00

What about sink preview? Is it rendering fine or it errors out?

Please post screenshot of sink settings ,dataset and data preview to investigate further.
Junaib v m 6 Reputation points

2022-07-21T15:27:49.793+00:00
Junaib v m 6 Reputation points

2022-07-21T15:42:21.69+00:00
Subashri Vasudevan 11,226 Reputation points

2022-07-21T16:17:47.173+00:00

Request you to share the schema of the sink data set.
Junaib v m 6 Reputation points

2022-07-21T16:45:08.033+00:00
Subashri Vasudevan 11,226 Reputation points

2022-07-22T05:43:44.767+00:00

Hi,

What are you doing in the pre SQL script ? Can you please check if there is a reference to VALUE column in your pre SQL script
Junaib v m 6 Reputation points

2022-07-22T06:30:41.203+00:00

Pre SQL and Post SQL are empty
Subashri Vasudevan 11,226 Reputation points

2022-07-22T06:54:05.163+00:00

It's surprising.

Pls try to delete the script text area shown below and close the browser and try debugging again. Could be a caching issue. But let's see.
Junaib v m 6 Reputation points

2022-07-22T08:12:49.237+00:00

Tried the above workaround, But getting same error.
Subashri Vasudevan 11,226 Reputation points

2022-07-22T10:28:32.487+00:00

@ShaikMaheer-MSFT request you to advise further on the issue.

Answer 1

ShaikMaheer-MSFT 38,516 Microsoft Employee

Hi @Junaib v m ,

Thanks for posting query in Microsoft Q&A Platform.

In your screenshots screenshot of Sink transformation preview is missing. I am suspecting issue might be with some expression may be in derived column transformation.

Are you able to perform data preview at each and every transformation in data flow? Kindly check it and see if that helps.

Please let us know how it goes. If you found issue already and had fix. Please feel free to share here with community. That help community. Thank you.

----------

Please consider hitting Accept Answer button. Accepted answers help community as well.

ShaikMaheer-MSFT 38,516 Reputation points Microsoft Employee

2022-08-03T05:58:58.983+00:00

Hi @Junaib v m - Just checking in to see if the above answer helped. If this answers your query, do click and upvote for the same. And, if you have any further query do let us know.
Junaib v m 6 Reputation points

2022-08-04T04:13:15.49+00:00

Hi @ShaikMaheer-MSFT ,

I will update the screen shots which u asked.
The issue is still not resolved.
Now iam attending a training.
Junaib v m 6 Reputation points

2022-08-04T04:27:18.66+00:00

I will update the details once the training is completed.

Thank you for your support @ShaikMaheer-MSFT
Junaib v m 6 Reputation points

2022-08-04T15:58:00.22+00:00
Hai @ShaikMaheer-MSFT ,

Let me explain this error with a sample data load.

1: Source :CSV (Without header)2 columns only
2:Destination:SQL MI
SQL SCRIPT :
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].recipes ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Scenarion 1: Destination Table is empty.
a: ADF executed and data loaded to sql MI without error.

Scenario 2: null value into any of the column.

a: ADF failed with error :DetailsJob failed due to reason: at Sink 'sink1':

java.sql.BatchUpdateException: Cannot insert the value NULL into column 'recipe_name',
table 'ETL.dbo.recipes'; column does not allow nulls. INSERT fails.

The above error is expected ,When inserting NULL in to a not nullable column we should get above error.
SO to remove these type of rows i enabled the error handling in SINK.
That is i need to write these type of sink errors into a separate error file.

After enabling error handling iam getting below error.

Error codeDFExecutorUserError

Troubleshooting guide

Failure typeUser configuration issue

DetailsJob failed due to reason: at Sink 'sink1': shaded.msdataflow.com.microsoft.sqlserver.jdbc.SQLServerException: Invalid column name 'VALUE'.

Source

Pipeline

pipeline3

Data flow

dataflow1

MonitorData flow activity
dataflow1

So here my findings are after enabling error handling iam getting this error.
Junaib v m 6 Reputation points

2022-08-04T16:05:03.333+00:00

1
Junaib v m 6 Reputation points

2022-08-04T16:05:28.757+00:00

2
Junaib v m 6 Reputation points

2022-08-04T16:06:04.597+00:00

3
Junaib v m 6 Reputation points

2022-08-04T16:06:27.063+00:00
Junaib v m 6 Reputation points

2022-08-04T16:12:52.033+00:00

1
Junaib v m 6 Reputation points

2022-08-04T16:13:09.743+00:00

2
Junaib v m 6 Reputation points

2022-08-04T16:13:29.68+00:00

5
Junaib v m 6 Reputation points

2022-08-04T16:13:47.273+00:00

5
Junaib v m 6 Reputation points

2022-08-04T16:14:06.65+00:00

6
Junaib v m 6 Reputation points

2022-08-04T16:14:26.07+00:00

1
Junaib v m 6 Reputation points

2022-08-04T16:14:52.013+00:00

9
Junaib v m 6 Reputation points

2022-08-04T16:15:31.3+00:00

9
Junaib v m 6 Reputation points

2022-08-04T16:15:58.457+00:00

1

Answer 2

Junaib v m 6

HI @Subashri Vasudevan @ShaikMaheer-MSFT

Microsoft is confirmed that this is a product bug and they provided the bug fix also for this ticket.

Error Reason:
ADF with SQL MI only support SQL collation CI_AS, but we are using CS_AS.

So this issue is resolved now.

Thank you @Subashri Vasudevan
@ShaikMaheer-MSFT
for your support.

ShaikMaheer-MSFT 38,516 Reputation points Microsoft Employee

2022-12-20T05:15:38.483+00:00

Hi @Junaib v m - Thank you for sharing update. This helps community members as well.

Share via

Azure data factory data flow is failing while loading data from csv(without header) to SQL MI

2 answers

Your answer