DWCopyCommandOperationFailed String or binary data would be truncated while reading column of type

mikeburek — Wed, 14 Aug 2024 17:53:25 GMT

I got this in a Fabric pipeline going from an Azure Managed Instance SQL Server to a Fabric Warehouse.

The problem turned out to be the default collation of the SQL Server of SQL_Latin1_General_CP1_CI_AS being converted to the Fabric Warehouse (at this time only supported) default collation of Latin1_General_100_BIN2_UTF8.

https://learn.microsoft.com/en-us/fabric/data-warehouse/tables#collation

I used the "Auto create table" option.

One specific example, for me, was the en-dash (the shorter dash) character. The below was run in SSMS 19.3.

On collation SQL_Latin1_General_CP1_CI_AS in SQL Server

ASCII ('–') = 150
DATALENGTH ('–') = 1

On collation Latin1_General_100_BIN2_UTF8

ASCII ('–') = 226
DATALENGTH ('–') = 3

The original column in SQL Server was varchar(20). So, the pipeline created a new table with the column as varchar(20).

On the source SQL Server, the value did take up all 20 bytes of the varchar(20).

So when copying the data over, changing the collation caused the pipeline to fail with the error:

ErrorCode=DWCopyCommandOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message='DataWarehouse' Copy Command operation failed with error ''String or binary data would be truncated while reading column of type 'VARCHAR(20)'. Check ANSI_WARNINGS option. Underlying data description: file 'https://.../MSSQLImportCommand/...parquet', column '...'. Truncated value: '...'.

If I manually created the table on the Fabric Warehouse and made the column varchar(21) instead of varchar(20), the data would fit. Doing so, the DATALENGTH of the value in the column would now say 21 in the collation Latin1_General_100_BIN2_UTF8.

When I changed the pipeline to point to a Lakehouse table, the column was auto-created as varchar (8000).

Re: DWCopyCommandOperationFailed String or binary data would be truncated while reading column of type

Anonymous — Thu, 15 Aug 2024 01:23:55 GMT

Hi mikeburek

Thank you so much for sharing this question!

If you have any questions, please continue to use the forum to ask.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: DWCopyCommandOperationFailed String or binary data would be truncated while reading column of type

KleberR — Sun, 13 Jul 2025 14:56:26 GMT

Use Copy command with mapped fields. That should do the trick.

topic DWCopyCommandOperationFailed String or binary data would be truncated while reading column of type in Pipelines

DWCopyCommandOperationFailed String or binary data would be truncated while reading column of type

Re: DWCopyCommandOperationFailed String or binary data would be truncated while reading column of type

Re: DWCopyCommandOperationFailed String or binary data would be truncated while reading column of type