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.

Error – External table is not in a valid format when you access an excel file

There could be many reasons for this error; one of them is that the file is locked by another user, for example your file is on a shared drive and another user has the file opened, or the file was uploaded or created by another application and the application still has a lock on it, if this was a one time load then you just need to copy the file and load the copied version of the file.

In case this was an automated recurring process then every time you need to copy the file to another folder, load the copied file then delete it.

hope this is helpful!