how can I strip unprintable ascii characters from a flat file transformation in ssis

Hi @Jeff Franken,

As @Viorel mentioned, maybe "Unprintable ASCII characters" are needed. In such case you could try to change (1) Locale, (2) Code page, or (3) Unicode settings which are properties to a file in a Flat File Connection Manager Editor. Please see below.

As an alternative, you can use SSIS Derived Column Task, and use REPLACE() function in its Expression.

For the reference: https://learn.microsoft.com/en-us/sql/integration-services/expressions/replace-ssis-expression?view=sql-server-ver17

Hi @Jeff Franken,

You can handle this directly within SSIS by adding a Script Component Transformation to clean your data before writing it to the destination flat file. This ensures all unprintable ASCII characters are safely removed and prevents truncation issues.

1. Add a Script Component in your Data Flow and select it as a Transformation.
2. Connect your source to the Script Component.
3. Select the columns you want to clean as input columns.
4. In the script editor, add the following code inside the Input0_ProcessInputRow method:
5. public override void Input0_ProcessInputRow(Input0Buffer Row)
6. {
7.     if (!Row.MyColumn_IsNull)
8.     {
9.         // Remove all non-printable ASCII/control characters
10.         Row.MyColumn = new string(
11.             Row.MyColumn.Where(c => !char.IsControl(c)).ToArray()
12.         );
13.     }
14. }

Replace MyColumn with your actual column name

1. Save, close, and re-run your package.

Thanks,
Akhil.