I have an AS400 source where the connection manager being used is the .NET provider for ODBC. The destination is a SQL Server 2005 table. Some of the source data contains some kind of character at the end of the string that I have not been able to remove. When I try to import the data into the SQL Server table, the package fails on a truncation error because of these extra characters.
When I take a sample of the offending data from my error output, I can see that the string causing the failure does not exceede the width of the destination column.
I've tried to exprement with different code pages (65001 and 37), but nothing has worked. I've tried using TRIM and RTRIM in the Derived Column transform, but that has not worked.