In SQL 2005, the Derived Column Transformation used to drive me nuts because any time you changed a string value, the component automatically changed the datatype to Unicode, even if it was originally string. This meant any time you changed an Expression that returned a non-unicode string, you had to change it back.
In SQL2008 it has got worse! Now the same change of data type is forced on you, but now the only way to change the data type back is to go into the Advanced Editor and amend the data type of the Output Column there, because – apparently by design – the data type is now locked in the normal UI.
Why by design? No idea, but according to this Connect article, that’s the way it’s going to stay – please vote this up and maybe Microsoft will revisit this incredibly annoying “feature”.
Update: I’ve also discovered that even if you drag a non-unicode column from the data flow into the Expression (nothing else, just the column) it still converts it to Unicode!