posted on Friday, January 13, 2006 6:49 PM
by
bknight
Proper Case for Data in a SSIS Script Component Transform
Well I feel like a college professor that shows the student a five page math problem and then follows up with a single button you could hit in your calculator to do the same thing. I used to hate that and now I'm that guy :). Anyways, in yesterday's post I showed how to do string conversions with the proper case (first letter in every word capitalized) by using regular expressions. While that was a pretty elogate way to do it, you can perform the same function with a simple built-in function called VbStrConv.ProperCase in the SSIS data flow. This function lets you take the input of a string and converts the string to the proper case. In the following code, you can see I have the input column called InputName and then I massage the data and output the value of OutputName.
The code then shrinks down to a single important line shown below in the commented line:
Imports
System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Public Class ScriptMain
Inherits UserComponent
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
'This is the line that performs the magic to Proper Case.
Row.OutputName = StrConv(Row.InputName, VbStrConv.ProperCase)
End Sub
End
Class
PS: Special thanks to Toby Rogers for catching how easy this would be to use VbStrConv instead of Regular Expressions!
-- Brian Knight