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, recently 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