Replacing a Non-Printing Character in SSIS Import

SSIS and Import/Export
1

Hello,

A non-ASCII character in a flat file is causing a partial load to a destination table. Currently to solve this, the non printing character is located via a Notepad ++ search and the 'weird' apostrophe is manually being replaced with a regular apostrophe.

The special character in the flat file that is causing the import failure/partial load is:
image

When we manually replace it with the following character, the entire flat file imports to the destination table successfully:
image

Is there a script or a step that can be added to the SSIS package to replace this non-printing weird apostrophe with a regular apostrophe so that it doesn't cause a partial load when importing the flat file? It would be nice to have this issue handled automatically without manual intervention.

Thank you in advance.

2

What is the destination columns data type? Varchar or nvarchar?

3

The destination column data type is Varchar.

Thank you.

4

Why don't you create a Derived Column with a convert and use the Derived Column instead of the

(DT_STR, «length», «code_page»)
and
REPLACE( «character_expression», «search_expression», «replace_expression» )