SQLTeam.com | Weblogs | Forums

Replacing a Non-Printing Character in SSIS Import


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:

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

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.

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

The destination column data type is Varchar.

Thank you.

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

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