Fault tolerance - Synapse Copy data activity

Question

Fault tolerance - Synapse Copy data activity

Nagesh CL 701

Hi Team,

I have enabled fault tolerance on copy data activity to "Skip incompatible rows" to blob storage.

Data truncation errors are stored in 2 different files: -

QID1111_1_2_Error.txt - Error message for the skipped rows are stored in this file.

QID1111_1_2_Row.txt - Error records are stored in this file.

User's image

Let's say there were 5 rows which had data truncation issues. Both the files will have 5 rows each (5 records on error file with just the error message and 5 records on the rows file with just the rejected row. The question is, how do we merge these files? We would need the error message why a particular row was skipped.

User's image

MartinJaffer-MSFT 26,161 Reputation points

2023-03-06T18:00:07.5466667+00:00

@Nagesh CL Hello and welcome to Microsoft Q&A.

As I understand, you are using Copy activity in Synapse and you error logs are splitting into separate error and data files. You wish to have a single, unified log file. Either by a second operation, or as directly output by the Copy activity.

My last understanding expected only 1 file. The docs seem to support that idea as well. However given your evidence, there must be an exception. As you are getting length truncation errors, I assume your sink is a database. The sinks sometimes can alter the logging, as in the case of Synapse Dedicated SQL Pool sink using Polybase or Copy command.

Could you please inform me which sink and copy method you are using? This will help me narrow down which combination outputs 1 vs 2 files.

As for combining the files after the fact, we could do this in Data Flow, but that is more than you need. Could also do it in Synapse notebooks. Or even in Cloud Shell or Excel. Which do you prefer?
Nagesh CL 701 Reputation points

2023-03-07T05:56:27.8166667+00:00

Hi @MartinJaffer-MSFT ,

Thanks for responding. Below are the details that you requested for: -

Your understanding is right, I am using copy data activity with: -

Source - ADLS Gen 2

Sink - Dedicated SQL Pool

ETL - Synapse Pipelines

Copy Method - Tried using both "Copy Command" and also "Polybase".

Enabled Fault Tolerance to handle data errors and log the error records (PFB the screenshot).

After I have enabled fault tolerance, error rows are written at two different folders: -

DataType / Format mismatches - For ex: - I have the data type of my sink as Date and if there is alphanumeric / non-date data in the file, then errors are logged at this location: -

The error file at this location contains error records and the error message on the same row. Just 1 file with both error record and error message for the same record (This is how it should work).

Data truncation issues - If there are any data truncates, then two files are written at a different folder (_rejectedrows). One with just the error records and the other with just the error message. It does not make sense why the error records and the corresponding error message are kept in two different files? Let's say if there are many rows that got rejected and if we have to know what's the issue with this row, Isn't it manual and cumbersome to go to a particular line on the error file and locate the error message?

Thanks.

Regards,

Nagesh CL
MartinJaffer-MSFT 26,161 Reputation points

2023-03-07T22:11:42.6733333+00:00

@Nagesh CL In my efforts, I have seen Copy fail on the truncation error, and not write any data, but writing the type error to log. Sometimes I get a text file with only header, no data.

BCP seems intolerant of truncation error, logs type error, writes nothing.

Copy Command with staging showed me the extra folder and log and error file you talked about. It also said it copied all 4 rows of my test, but the table is still empty! The Copy failed.

I had a thought on why the two errors are reported separately. Data Factory / Synapse does the type conversion in mapping phase, so the type conversion error comes from Factory/Synapse.

However, the truncation error is coming from the SQL server, as that is where the restriction is. Factory/Synapse doesn't do the truncation. The difference in sources mean different channels, so that is probably why there are multiple error/log files.

I still agree with you that a unified error/log file is optimal.

To test my idea on separate sources, I'm going to deliberately mess with my dataset schema and mapping, so it passes through chars instead of ints. Thus the copy activity passes bad data. I expect both errors to appear in the same logfile.
MartinJaffer-MSFT 26,161 Reputation points

2023-03-08T00:54:39.1566667+00:00
Okay, I got some results. Found that I needed to set maxerrors to get anything written. Also Copy command sure is picky. Anyway, my sink table has an int column and a char1 column. After editing the schemas and mapping to treat both as strings, I got a difference in the error files. Also the copy activity succeeded.

Error:

Bulk load data conversion error (truncation) for row starting at byte offset 17, column 2 (Text) in data file errortest.csv. Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row starting at byte offset 32, column 1 (Num) in data file errortest.csv.

Row:

3,CDEFGHIJKLMN Z,0

This time none of the errors appeared outside the _rejectedrows folder. Since both error types now appeared in the same place, this validates my hypothesis of "different source, different place".
Nagesh CL 701 Reputation points

2023-03-08T05:59:05.9466667+00:00

Hi @MartinJaffer-MSFT , glad you were able to reproduce the issue.

Is there a link / support page where we can raise this issue to have one unified error log (As a product recommendation / feedback) with rejected records and its corresponding error message?

Regards,

Nagesh CL
MartinJaffer-MSFT 26,161 Reputation points

2023-03-15T19:42:18.6333333+00:00

@Nagesh CL Yes, there are several places to submit feedback. One of them is right in the ADF/Synapse workspace. I have circled the icon in below snapshot.

I will also submit feature request on my end. But you should still submit too! The more requests, the more likely it happens.
Nagesh CL 701 Reputation points

2023-03-27T06:48:39.4333333+00:00

Thank you @MartinJaffer-MSFT

Your answer

MartinJaffer-MSFT 26,161 Reputation points

2023-03-06T18:00:07.5466667+00:00

@Nagesh CL Hello and welcome to Microsoft Q&A.

As I understand, you are using Copy activity in Synapse and you error logs are splitting into separate error and data files. You wish to have a single, unified log file. Either by a second operation, or as directly output by the Copy activity.

My last understanding expected only 1 file. The docs seem to support that idea as well. However given your evidence, there must be an exception. As you are getting length truncation errors, I assume your sink is a database. The sinks sometimes can alter the logging, as in the case of Synapse Dedicated SQL Pool sink using Polybase or Copy command.

Could you please inform me which sink and copy method you are using? This will help me narrow down which combination outputs 1 vs 2 files.

As for combining the files after the fact, we could do this in Data Flow, but that is more than you need. Could also do it in Synapse notebooks. Or even in Cloud Shell or Excel. Which do you prefer?
Nagesh CL 701 Reputation points

2023-03-07T05:56:27.8166667+00:00

Hi @MartinJaffer-MSFT ,

Thanks for responding. Below are the details that you requested for: -

Your understanding is right, I am using copy data activity with: -

Source - ADLS Gen 2

Sink - Dedicated SQL Pool

ETL - Synapse Pipelines

Copy Method - Tried using both "Copy Command" and also "Polybase".

Enabled Fault Tolerance to handle data errors and log the error records (PFB the screenshot).

After I have enabled fault tolerance, error rows are written at two different folders: -

DataType / Format mismatches - For ex: - I have the data type of my sink as Date and if there is alphanumeric / non-date data in the file, then errors are logged at this location: -

The error file at this location contains error records and the error message on the same row. Just 1 file with both error record and error message for the same record (This is how it should work).

Data truncation issues - If there are any data truncates, then two files are written at a different folder (_rejectedrows). One with just the error records and the other with just the error message. It does not make sense why the error records and the corresponding error message are kept in two different files? Let's say if there are many rows that got rejected and if we have to know what's the issue with this row, Isn't it manual and cumbersome to go to a particular line on the error file and locate the error message?

Thanks.

Regards,

Nagesh CL
MartinJaffer-MSFT 26,161 Reputation points

2023-03-07T22:11:42.6733333+00:00

@Nagesh CL In my efforts, I have seen Copy fail on the truncation error, and not write any data, but writing the type error to log. Sometimes I get a text file with only header, no data.

BCP seems intolerant of truncation error, logs type error, writes nothing.

Copy Command with staging showed me the extra folder and log and error file you talked about. It also said it copied all 4 rows of my test, but the table is still empty! The Copy failed.

I had a thought on why the two errors are reported separately. Data Factory / Synapse does the type conversion in mapping phase, so the type conversion error comes from Factory/Synapse.

However, the truncation error is coming from the SQL server, as that is where the restriction is. Factory/Synapse doesn't do the truncation. The difference in sources mean different channels, so that is probably why there are multiple error/log files.

I still agree with you that a unified error/log file is optimal.

To test my idea on separate sources, I'm going to deliberately mess with my dataset schema and mapping, so it passes through chars instead of ints. Thus the copy activity passes bad data. I expect both errors to appear in the same logfile.
MartinJaffer-MSFT 26,161 Reputation points

2023-03-08T00:54:39.1566667+00:00

Okay, I got some results. Found that I needed to set maxerrors to get anything written. Also Copy command sure is picky. Anyway, my sink table has an int column and a char1 column. After editing the schemas and mapping to treat both as strings, I got a difference in the error files. Also the copy activity succeeded.

Error:

Bulk load data conversion error (truncation) for row starting at byte offset 17, column 2 (Text) in data file errortest.csv. Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row starting at byte offset 32, column 1 (Num) in data file errortest.csv.

Row:

3,CDEFGHIJKLMN Z,0

This time none of the errors appeared outside the _rejectedrows folder. Since both error types now appeared in the same place, this validates my hypothesis of "different source, different place".
Nagesh CL 701 Reputation points

2023-03-08T05:59:05.9466667+00:00

Hi @MartinJaffer-MSFT , glad you were able to reproduce the issue.

Is there a link / support page where we can raise this issue to have one unified error log (As a product recommendation / feedback) with rejected records and its corresponding error message?

Regards,

Nagesh CL
MartinJaffer-MSFT 26,161 Reputation points

2023-03-15T19:42:18.6333333+00:00

@Nagesh CL Yes, there are several places to submit feedback. One of them is right in the ADF/Synapse workspace. I have circled the icon in below snapshot.

I will also submit feature request on my end. But you should still submit too! The more requests, the more likely it happens.
Nagesh CL 701 Reputation points

2023-03-27T06:48:39.4333333+00:00

Thank you @MartinJaffer-MSFT

Share via

Fault tolerance - Synapse Copy data activity

Your answer