Truncate Error when running Dataflow GEN2 with insufficient information to diagnose the issue.

[SuzeK]

I have a DataFlow GEN2.   I am getting the following error with no clue as to which column or even query.   I get that the issue is that it is having to truncate data, but I don't know which query or which column.   How can I find out more about the error so that I can sort this out?

WriteToDataDestination,2025-04-29T12:06:23.2771603+00:00,2025-04-29T12:06:28.6522604+00:00,00:00:05,NA,Failed,"There was a problem refreshing the dataflow: 'Couldn't refresh the entity because of an issue with the mashup document MashupException.Error: DataSource.Error: Microsoft SQL: String or binary data would be truncated.
Statement ID: {4E73ABEF} | Query hash: 0xA07BE32 | Distributed request ID: {1D8CE1CA} Details: Reason = DataSource.Error;ErrorCode = 10478;DataSourceKind = Warehouse;DataSourcePath = Warehouse;DataSourceKind.2 = SQL;DataSourcePath.2 = m3kejfpwmxoupgrqwwtwg4k6py-tfcmklttojtefczjheg3hbbinm.datawarehouse.fabric.microsoft.com;Reporting_Silver_Warehouse;Message = String or binary data would be truncated.
Statement ID: {4E73ABEF-BB180D605213} | Query hash: 0x3210FCE04A07BE32 | Distributed request ID: {1D8CE1CA};ErrorCode = -2146232060;Number = 8152;Class = 16;State = 14;ConnectionId = 31701803-bf8d;Microsoft.Data.Mashup.Error.Context = User'. Error code: 104100."

[SuzeK · Accepted solution]

I solved the issue of the error, but have not found a way to get the erroring column name. 

To solve it without this basic information, I did the following:

1. Copied the Dataflow and removed all the non-erroring queries.
2. Changed the name of the queries so that they would output to new tables
3. Generated a query that would find the max string lengths of all of the text columns in a single table
4. Ran that and compared that to the column definitions of the output table.
5. Did the last two steps for each one.
6. Updated the output table definitions as necessary

I created a query that would find the max string length of all rows in a table for the string columns.  

select id from sysobjects where name = 'my_erroring_table'
select STRING_AGG('max(len(isnull('+[name]+',''''))) as max' + [name], ',') as bbb from syscolumns where id =219147826 and xtype=167

This creates the sql which you can then use to get the max of those columns so just put "select " and  "from my_erroring_table"

I hope this helps anyone having the same issue.

[lbendlin]

From what I remember "nvarchar(max)"  in Fabric seems to actually be "nvarchar(4000)"   - is your string longer than that?

[SuzeK]

Hi, no.  I am now able to see which query is causes the problem.  I put the error data into Excel and that makes it clearer.   My issue is that I dont know which column so that I can diagnose the issue.   None of the data is particularly big.  I am going to have to copy my Dataflow, in the new one, delete all the queries except the erroring and then play around with it until it works.  This is crazy.  This would be so much easier if I knew the column.