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.
Advertisements

3 thoughts on “SSIS – Global replace for all columns using a script component

  1. Thank you for this article. I’ve been hunting all afternoon for a solution to my problem. I think this is just the ticket. However, I’m new to scripting, so forgive me if this is a basic question. I did a “Bingle” search for it, but didn’t find anything helpful. When I copy in the above code to my Script component, I get an error on PropertyInfo (the red squiggly underline) that says “The type or namespace name ‘PropertyInfo’ could not be found.” I’m sure it’s something simple, but could you possible tell me what I’ve done wrong?

    TIA,
    Wayne

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s