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.

Why can’t I a have string? aka Nullable<string>

It’s not often that I write a post to rant about something that is by design and not actually a bug or just hard to accomplish end but something that really bothers me is the fact I can’t declare a string nullable.

The number one response to that is ”surely you jest as string is already nullable. Which obviously it is, however it’s also created to behave as a primitive type. This being the case makes it the only primitive type you can’t declare as nullable. Once again people usually will re-iterate, ‘exactly, it’s already nullable.’

If string is nullable, why can’t I preform .GetValueOrDefault()?

This is one of the most useful methods available as it easily let you establish frameworks around your primitive types without ever once needing to worry about triggering a null reference exception. It also clearly defines all objects that you expect to be able to be uninitialized and null. This is why I hate that strings cannot be nullable, it’s impossible to tell the intent of the string. Is null an accepted value because it could imply string.Empty? This always has to be handled by convention and validation there’s no way to clearly define this, whereas if you could declare string? this would definitively tell you that null is an expected value and acceptable.

To further my proof is the static method on the string class, string.IsNullOrEmpty(string). This method was placed here because the C# team clearly knew that strings being complex objects treated as reference types is very ambiguous and knew to expose a standardization on how to check this. Why would none of the other classes provide a type.IsDefault(type) method? So in my conclusion I should be able to do string? and sadly I can’t and I guess would have very little regress other than perhaps downloading mono and probably needing to alter the source code for itself.