SSIS String: Remove spaces from all string columns

Do ‘something’ for all columns in your dataflow

 Case
If you have a lot columns that all need to be adjusted with the same function (let’s say uppercase). You could use the Derived Column component or the Character Map component to adjust all columns one by one. The Script Component can make your life easier.Starting point

Col1 Col2 Col3
test Test TEST
bla Bla BLA
xxx Xxx XXX

Desired situation

Col1 Col2 Col3
TEST TEST TEST
BLA BLA BLA
XXX XXX XXX

Solution
1) Add a script component to you dataflow.

Script component (transformation)

2) Select all columns (you need) and select ReadWrite.

ReadWrite Input columns
// This script adjusts the value of all string fields
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.Reflection;            // Added
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    //  Method that will be started for each record in you dataflow
    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        // Use Reflection to loop through all the properties of Row:
        // Example:
        // Row.Field1 (String)
        // Row.Field1_IsNull (Boolean)
        // Row.Field2 (String)
        // Row.Field2_IsNull (Boolean)
        foreach (PropertyInfo p in Row.GetType().GetProperties())
        {
            // Do something for all string properties: Row.Field1, Row.Field2, etc.
            if (object.ReferenceEquals(p.PropertyType, typeof(string)))
            {
                // Use a method to set the value of each String type property
                // Make sure the length of the new value doesn't exceed the column size
                p.SetValue(Row, DoSomething(p.GetValue(Row, null).ToString()), null);
            }
        }
    }
 
    // New function that you can adjust to suit your needs
    public string DoSomething(string ValueOfProperty)
    {
        // Uppercase the value
        ValueOfProperty = ValueOfProperty.ToUpper();
                  // trim leading and trailing spaces
ValueOfProperty = ValueOfProperty.Trim();
        return ValueOfProperty;
    }
}

4) Now add a target and run the package to see the result:

Added two data viewers to see the result.
Advertisements

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