Non-ascii (control characters) in SQL field.
Normally, I wouldn't worry about these, however, if you are running a Data Warehouse and use these as dimension attributes, keys, etc. OLAP processing will have a fit!
Since a couple of my feeds come from a web-based application server, there is always the potential for CR, LF, TABS and other strange non-compliant characters.
The other place this gets you is in reporting, sometimes you end up with a FF (Form-Feed) character that the printer recognizes and boom, your report now has a blank page or two in the middle of it.
To reduce this I have created a CLR function that uses REGEX to remove all non printing characters from the string value.
Doing this does a couple of things, A) remove the unwanted character and B) converts from nchar/nvarchar to char/varchar and finally C) Handles nulls (I hate nulls) esp in OLAP Data Warehousing.
As to the original post on the SSIS component, I default to the CAST/CONVERT of SQL server to deal with most of this, in getting the output fields to match, I take the source SQL and use a simple SELECT 1 AS A , then I click on the link (between source and destination) then remove unmapped fields, this remove the now incompatible types. I then replace my simple select with my true select and remap the fields.
This approach works for me as I am in control of both the SOURCE and DESTINATION schema, so the column mapping happens automagically due to the names being identical. This may not be the best approach for all situations.