CODEWITHSUNDEEP
azureazure-data-factoryazure-synapse
Jayhoffa75
Jayhoffa75

Reputation: 31

Data Factory Won't Load Large Column

We've got an Azure SQL table I'm moving from the traditional DB into a Azure Synapse Analytics (DW) table for long term storage and so it can be removed from our production DB. This is a system table for a deprecated system we used to use (Salesforce). I've got a column in this table in the DB that is a varchar(max), and its massive. The MAX(LEN(FIELD) is 1585521. I've tried using Data Factory to move the table into the DW, but it fails on that massive column. I modeled the DW table to be a mirror of the production DB table, but it fails to load and have tried several times. I changed the DW column that is failing to nvarchar(max), but its still failing (thought it might be non-unicode causing the failure). Any ideas? Its confusing me because the data exists in our production DB, but won't be nice and peacefully move to our DW.

I've tried several times and have received these error messages (second one after changing the DW column from varchar(max) to nvarchar(max):

HadoopSqlException: Arithmetic overflow error converting expression to data type NVARCHAR."}

HadoopExecutionException: Too long string in column [-1]: Actual len = [4977]. MaxLEN=[4000]

Upvotes: 0

Views: 1304

Answers (1)

HarithaMaddi-MSFT
HarithaMaddi-MSFT

Reputation: 551

Currently using Polybase has this limitation of 1mb and length of column is greater than that. The work around is to use bulk insert in copy activity of ADF or chunk the source data into 8K columns and load into target staging table with 8K columns as well. Check this document for more details on this limitation.

If you're using PolyBase external tables to load your tables, the defined length of the table row can't exceed 1 MB. When a row with variable-length data exceeds 1 MB, you can load the row with BCP, but not with PolyBase.

It worked for me when I used "bulk insert" option in the ADF pipeline.

Upvotes: 1

Related Questions