Making text proper case in SSIS 2008 with a script component

Drop a new script component on your dataflow task and wire up the input to it. Select the input column that you want to be proper cased. Also set an output column that you will want the correctly cased text to flow from (or you could set it up to alter your input column if you wanted)

After that edit your script so the script editor opens up, the first thing you need to do is right click on the references folder on the right and goto add reference and under the .NET tab pick Microsoft.VisualBasic. Then your code should look similar to

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using Microsoft.VisualBasic;
using System.Globalization;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        Row.Name = Strings.StrConv(Row.County, VbStrConv.ProperCase,CultureInfo.CurrentCulture.LCID);
    }
}

This should do it, the properties you need to use Row.Name and Row.County those are all dependent on the columns you specify for your input and output mapping.

Advertisements

One thought on “Making text proper case in SSIS 2008 with a script component

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