Anyway to set a column in SSIS in proper case format (i.e, captilize the
first letter in each word. Was hoping on a function within the conversion
or derived data transformation tasks

Change JOHN SMITH to John Smith

thanks,

There is no "Proper Case" transformation but you could certainly easily
create your own in a custom component or even using the Script component
as a transform.

Allan






"Dave L" <DaveL (AT) discussions (DOT) microsoft.com> wrote

The Framework has a Title case method somewhere in System.Text I seem to
recall. This could be leveraged in a Script Transform without too much
hassle.

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

You could try the following:

CREATE FUNCTION fn_TitleCase (@instr varchar(1000))
RETURNS varchar(1000)
AS
Begin
Declare @Pos integer

If @instr Is Null Return @instr
If Len(@instr)=0 Return @instr

Set @instr= Upper(Substring(@instr,1,1)) +
Substring(@instr,2,Len(@instr)-1)
Set @Pos = 2
While @Pos < Len(@instr)+1
Begin
if Substring(@instr,@Pos-1,1)=' ' Or Substring(@instr,@Pos-1,1)='.'
Set @instr= Substring(@instr,1,@Pos-1)
+ Upper(Substring(@instr,@Pos,1))
+ Substring(@instr,@Pos+1,Len(@instr)-@Pos)
Else
Set @instr= Substring(@instr,1,@Pos-1)
+ Lower(Substring(@instr,@Pos,1))
+ Substring(@instr,@Pos+1,Len(@instr)-@Pos)
Set @Pos=@Pos+1
End
Return @instr
End




"Darren Green" <darren.green (AT) reply-to-newsgroup-sqldts (DOT) com> wrote