SSIS Error: Text was truncated or one or more characters had no match in the target code page
This error started generating since last 2 days on a scheduled job that uses SSIS to import content from a flat file. Eventually we found that a derived column within the table and the flat file flow where dates in YYYYMMDD are changed to MM/DD/YYYY format. Then the string MM/DD/YYYY is converted to a date in a Data Conversion flow. And finally the data is put into a SQL Server table (currently with no rows). There is a temparory table involved here to import rows and then eventually the relevant data will be moved to master tables on the database.
After digging out that SSIS job the problem occurs with a text field with the email address in it. Error is "Text was truncated or one or more characters had no match in the target code page.". The calculation of number of characters for that column in the table matches with the source file. Then the analysis shifted to '..no match in target code page', I have checked the code page for source file and code page in destination table, there the difference exists 1252 and 1255. Recently the code page on this destination table the code page is modified to allow unicode data from other sources. As per the error code page & Collation on the source and target table are matched and the issue is resolved.
Also I found out another option to try is to do the string conversion outside of SSIS. For example, using a select statement with a cast in the source adapter that reads the data into IS already as DT_STR (the cast outside of IS may fail for the same reason which would also help you narrow this down).
Further information on the error from this BlogPost too.