Cant change data type in Derived Column transform - by TimGarrod

Status : 

  By Design<br /><br />
		The product team believes this item works according to its intended design.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 336139 Comments
Status Closed Workarounds
Type Bug Repros 8
Opened 3/31/2008 8:23:25 AM
Access Restriction Public


In SSIS 2005 when adding a column using a derived column you can change the data type (example - a static string column change be changed from Unicode to Non-Unicode string type) simply by changing the datatype in the drop down list.  This functionality doesn't exist in 2008 Feb CTP.
Sign in to post a comment.
Posted by Peter_D503 on 11/8/2012 at 4:40 PM
This is ridiculous, why am I forced to use a Unicode string?
Posted by Russ Loski on 10/25/2012 at 6:17 PM
This is truly a poor design decision.
Posted by pjdeano on 4/23/2012 at 12:59 PM
thanks microsoft! you took a task from 30 minutes and turned it into 1 hour! stay productive!
Posted by Kenneth Coggin on 4/16/2010 at 7:13 AM
I am working on the Tutorial: Creating a Basic Package Using a Wizard, Lesson 2, Task 4 it still says to edit the length in the Derived Column Transform. However this says that is not possible.
Also in Task 3, it says to place the Sort Transform under the Data Conversion Transform... Then it states to connect the Source - Query to the Sort, and never makes mention of the Data Conversion again. When you get to the end of the Lesson, it shows you the Design View of the lesson and the Data Conversion Transform is no where to be found... Is it not needed? Should it just be deleted?
Posted by NathanG on 2/21/2010 at 3:41 PM
Seems a strange design decision to me. When creating a derived column through string manipulation on an existing column of type DT_STR, the editor forces this to DT_WSTR unicode data type, so I have to explicitly include a type cast back to DT_STR in the expression? This just adds a lot of unnecessary type casting to already complex expressions.
Posted by ellen.jr on 11/19/2009 at 7:49 AM
This change has transformed a useful, easy-to-use component into an extremely awkward and unfriendly one. Even when first creating a derived column, you're not allowed to choose the data type you want. Requiring the use of explicitly entered Type Cast statements seems an ill-advised way to enforce external coding standards on SSIS developers.
Posted by Todd McDermid on 9/16/2009 at 10:10 AM
Using the Advanced Editor is a bad idea - it's extremely opaque and hidden away. The proper way to coerce type changes is to use explicit casts. For example, to coerce a type change from the (default) DT_WSTR of a string literal to a non-Unicode string type, explicitly cast it with the cast operators shown in the function list, like this:
(DT_STR, 1252)"Test Text"

The primary benefit of having to do things this way is that there is no longer any implicit casting - it's all explicit. You get what you asked for. It's also easier to debug someone else's code - because the cast is in the expression, not off the right-hand side of the screen.
Posted by BI Monkey on 9/16/2009 at 12:03 AM
Todd, agree, this is a ridiculous "feature" made all the more annoying because the component will force a datatype change on you that cannot be reversed.
Posted by Todd C on 2/17/2009 at 1:07 PM
I see that this issues has a status of "Closed (By Design)".

Can someone at Microsoft please explain WHY you allow a user to change the Data Type using the Advance Editor, but NOT the standard properties dialog box? Have you ever thought about re-writing the specifications of this componenet, then re-designing them to meet those specifications?

Data Types are SUCH a HUGE aspect of ANY SSIS package that FAILURE to allow easy editing of Data Types in the Derived Column Transform is nothing less than a lack of fore-thought!
Posted by Microsoft on 4/4/2008 at 9:43 AM

In SQL Server 2008, the behavior of the Derived Column transformation was changed. The data type, length, precision, and scale columns in the user interface are now read-only. I have corrected and updated the 2 Books Online topics that describe the previous behavior. Thank you for catching this inconsistency.

Posted by Microsoft on 3/31/2008 at 2:07 PM
Thanks for your feedback. We appreciate it.

We are currently investigating this issue and will upate you soon.

As a workaround, you can right click "Derived column" transform on your data flow task, "show advanced editor", select "Input and Output properties" tab, expand "Derived Column ouput" node and then change the data type of column ABC from DT_WSTR to DT_STR. This should work fine.