A blog about SQL Server, SSIS, C# and whatever else I happen to be dealing with in my professional life.

Find ramblings

Monday, April 26, 2010

SSIS Derived Column Transformation Editor, a turn for the worse in 2008

This year my company is finally moving forward with SQL Server 2008 and my current project is the first to use the 2008 SSIS engine.  In general, I haven't had that much of a geekgasm over it.  I made my peace with VB in the scripting components a long time ago so C# wasn't that big of an upsell.  This post however, is about a deficit in the SSIS Derived Column Transformation Editor in the 2008 world.  I might have also stumbled onto a bug which is exciting.  

Given the following expression, 
0 == 0 ? (DT_STR,3,1252)"pre" : (DT_STR,3,1252)"pst"
one would expect that to have a data type of non-unicode string (DT_STR) due to the explicit cast.  However, that is not the case, plug that expression into either 2005 or 2008 and it'll set the data type as Unicode string (DT_WSTR).  In 2005, you still had the option of "fixing" data types but that feature has been removed in 2008.  See attached screen shots.
The solution with much thanks to @VidasM is to cast the results of the ternary operator to string. I had tried that but operator precedence threw me off.
(DT_STR,3,1252)(0 == 0 ? "pre" : "pst"))

No comments: