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.

foreach (PropertyInfo dataColumn in Row.GetType().GetProperties())
{

    // skip columns end with _IsNull as these columns just indicate if the object has a null value
    //also look for columns with string datatype as numeric, booolean, date datat types can't have null as a string
    if (dataColumn.Name.ToLower().EndsWith("_isnull") == false && dataColumn.PropertyType == typeof(string))
    {
            object objValue = dataColumn.GetValue(Row, null);

            if (objValue != null)
            {
                 string value = objValue.ToString().ToUpper();
                 //set the new value
                 dataColumn.SetValue(Row, value, null);
            }
    }
}

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 value as we don’t need them here.
• always check that the value is not null before converting it to another data type.
• i’m only applying the change to columns with type of string, you might not need to do that or change it to another type.