SSIS – Global replace for all columns using a script component

Sometimes while in the data flow you need to apply the same function on all the columns in the data flow or all columns have a specific data type, for example:

1. Remove all commas and new lines from text columns before exporting them to a CSV file.
2. Replace text “Null” with Null value when loading data from an excel file that has nulls as text in it.
3. Check for each column if has a null value and replace it with a default value based on the data type.
4. Convert all strings to upper or lower case. etc…

The easiest way to do this is to add a script component then use the approach below, here i’m converting all strings to upper case.

few notes about the code above:

• i’m skipping all columns with name ends with “_isnull” as these columns just to indicate if the original column has a null…