ADF Data Flows, HadoopSqlException: Arithmetic overflow error converting expression to data type NVARCHAR at sink

STAKV 36

Hello,

I got the following error message thrown by a pipeline containing a data flow activity.

Operation on target df_stg_json_to_synapse_seedesadv failed: {"StatusCode":"DFExecutorUserError","Message":"Job failed due to reason: at Sink 'ItemSeedesadv': shaded.msdataflow.com.microsoft.sqlserver.jdbc.SQLServerException: HdfsBridge::recordReaderFillBuffer - Unexpected error encountered filling record reader buffer: HadoopSqlException: Arithmetic overflow error converting expression to data type NVARCHAR.","Details":"at Sink 'ItemSeedesadv': shaded.msdataflow.com.microsoft.sqlserver.jdbc.SQLServerException: HdfsBridge::recordReaderFillBuffer - Unexpected error encountered filling record reader buffer: HadoopSqlException: Arithmetic overflow error converting expression to data type NVARCHAR."}

The target table does not contain any NVARCHAR columns anymore. The dataset schema has been refreshed.

The target table structure is:

CREATE TABLE [stg].[ItemSeedesadv]
(
[File_Name] varchar NULL,
[Me_MessageNumber] varchar NULL,
[ItemPos_LineItemNumber] varchar NULL,
[ItemPos_LineSubItemNumber] varchar NULL,
[ItemPos_CountryOfOrigin] varchar NULL,
[ItemPos_ItemDescription] varchar NULL,
[Item_ItemNumber] varchar NULL,
[Item_Qty] varchar NULL,
[Item_AmountDetails] varchar NULL,
[Item_Ref] varchar NULL,
[Item_Date] varchar NULL,
[Item_Add] varchar NULL,
[Item_WeightAndVolume] varchar NULL,
[Item_Txt] varchar NULL,
[Item_DangerousGoods] varchar NULL,
[Item_Pac_Package] varchar NULL,
[Item_Pac_Pallet] varchar NULL
)
WITH
(
DISTRIBUTION = ROUND_ROBIN,
HEAP
)
GO

Pipeline Run ID: 03afc9e9-c156-4d86-bdc7-14c0a4ac095c
Data Flow Run ID: c0150967-1b45-4707-9f19-370d2bf3d529

Could you please investigate and advise?

Saurabh Sharma 23,671 Reputation points Microsoft Employee

2021-03-26T01:32:46.33+00:00

@STAKV Thanks for using Microsoft Q&A !!
You could get this overflow error message if the table has more than one records with very long TEXT then implicit cast gets applied to convert this text to nvarchar which generates this arithmetic overflow error. Can you please check if the any column has long text. Also, alternatively I suggest to open an Azure support case for this issue as someone has to look into your environment and data to investigate it further.
STAKV 36 Reputation points

2021-03-26T13:32:17.243+00:00

Thank you for the quick response, SaurabhSharma-msft.

Indeed, there could be long TEXT coming from the source files as they are complex hierarchical JSONs.

We have opened an Azure support case for further investigation.

I am going to post an update in case we find anything helpful and worth sharing.
Saurabh Sharma 23,671 Reputation points Microsoft Employee

2021-03-29T23:43:13.363+00:00

@STAKV sure, thanks for the updates.
Jignesh Rathod 1 Reputation point

2022-06-23T08:54:15.09+00:00

@STAKV @Saurabh Sharma
Hi, I am facing similar issue while trying to read some columns from a Parquet using external table in Synapse. I have also found the column which is having very long text and so i tried to reduce its length to 4000 in Databricks while creating the parquet. But still it is giving below error:
"HdfsBridge::recordReaderFillBuffer - Unexpected error encountered filling record reader buffer: HadoopSqlException: Arithmetic overflow error converting expression to data type NVARCHAR"

Any suggestion would be much appreciated..
Thank you!
Theodora Eleftheriou 0 Reputation points

2023-02-08T13:11:36.3433333+00:00

@STAKV we are currently having the same issue here. We were wondering whether you managed to fix this issue as I cannot see any solutions to this link. We have also opened a ticket with Microsoft support but unfortunately, it's been a while and we didn't come to a solution yet.

Any suggestions will be really appreciated. Thank you
Theodora Eleftheriou 0 Reputation points

2023-02-08T13:11:54.13+00:00

@STAKV we are currently having the same issue here. We were wondering whether you managed to fix this issue as I cannot see any solutions to this link. We have also opened a ticket with Microsoft support but unfortunately, it's been a while and we didn't come to a solution yet.

Any suggestions will be really appreciated. Thank you