I recently had an unusual problem with one of my SSIS packages after modifying the destination table layout from VARCHAR to NVARCHAR. The package included a data flow task that ran in-line SQL as an OLE DB Source. I was using a case statement to reformat the backupset. type column. Of course after changing the destination column from VARCHAR to NVARCHAR i received a warning regarding the inability to implicitly convert the input field to UNICODE and that the resulting conversion would be defaulted to DT_WSTR. This seemed perfectly acceptable to me as is the data type for UNICODE text. The package failed with the following error :
[OLE DB Source ] Error: There was an error with output column "backup_type" (343) on output "OLE DB Source Output" (277). The column status returned was: "Text was truncated or one or more characters had no match in the target code page.
So of course I immediately check the code page properties for the source and destination and both were set to a default code page of 1252. I then figured that there was something amiss with the implicit conversion to UNICODE, so I attempted and explicit conversion to NVARCHAR in my in-line SQL code. Again the package failed.
I inserted a Data Conversion task between the OLE DB Source and the OLE DB Destination that explicitly converted the offending column to [DT_WSTR] (UNICODE).