I've hit this error enough that I have developed a simple technique for troubleshooting this. It's not fancy, and you could probably think of something better, but this works and gets me to my resolution in a few seconds so I thought it would be worth sharing. The trick is to turn your "INSERT INTO (...) SELECT (...)" into a "SELECT (...) INTO _BadDataTable_ (...)" statement. The SELECT INTO will create a new table for you on the fly, assuming that you use the AS clause in your SELECT to name your fields the same as the destination table you're looking to INSERT INTO.
From there, you can then run this simple SQL script changing the values of @prodTable and @invalidTable into the real names of the destination table and the one you made on the fly containing the bad data. Kinda hackish, but it works and that's all I'm looking for. Note that you have to make a real table with the fields named the same for this to work. Feel free to take and modify to suit your needs, and here's to hoping the next release of SQL Server will fix this completely arcane error:
-- setup declare @prodTable varchar(100) = 'MyDestTable', -- CHANGE THIS!!! @invalidTable varchar(100) = '_BadDataTable_', -- AND THIS!!! @c cursor, @column_name varchar(255), @max_len int if object_id('tempdb..#max_len') is not null drop table #max_len create table #max_len (max_len int) -- get schema for prod table if object_id('tempdb..#prod_table_schema') is not null drop table #prod_table_schema select ordinal_position, column_name, data_type, character_maximum_length into #prod_table_schema from INFORMATION_SCHEMA.COLUMNS a where a.table_name = @prodTable order by 1 -- get schema for table made from invalid data with the trucate error if object_id('tempdb..#invalid_table_schema') is not null drop table #invalid_table_schema select ordinal_position, column_name, data_type, cast(null as int) as character_maximum_length, case when character_maximum_length is null then 0 else 1 end as is_char_field into #invalid_table_schema from INFORMATION_SCHEMA.COLUMNS a where a.table_name = @invalidTable order by 1 -- we need to chase after the max(len(COL)) info with a dynamic query set @c = cursor local fast_forward for select a.column_name from #invalid_table_schema a where a.is_char_field = 1 order by a.ordinal_position open @c fetch next from @c into @column_name while @@fetch_status = 0 begin -- get the maximum data length of the field from the table delete #max_len insert into #max_len exec('select max(len(' + @column_name + ')) as max_len from ' + @invalidTable) select @max_len = max_len from #max_len update #invalid_table_schema set character_maximum_length = @max_len where column_name = @column_name fetch next from @c into @column_name end close @c deallocate @c -- Tell me which fields have the problem select a.column_name, a.character_maximum_length, b.character_maximum_length as actual_length_of_data from #prod_table_schema a join #invalid_table_schema b on a.column_name = b.column_name where a.character_maximum_length < b.character_maximum_length