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

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).