SQL Server Home
Cant change data type in Derived Column transform
as By Design
3/31/2008 8:23:25 AM
User(s) can reproduce this bug
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.
SQL Server 2008 February CTP
Integration Services (DTS)
Windows XP Professional
Operating System Language
Steps to Reproduce
Create a data flow in 2005 - add a Derived Column transform, and add a column with a value of "ABC". Close and reopen the Derived Column transform - the data type can be changed from WSTR to STR using the datatype drop down.
Attempt to do the same in 2008 Feb CTP - the data type drop down selection functionality no longer exists.
Cannot change the data type except through Explicit conversion using DATATYPE conversion (eg. DT_STR(length, codepage).
Expect to be able to override / change datatype via drop down.
to post a comment.
Please enter a comment.
on 11/8/2012 at 4:40 PM
This is ridiculous, why am I forced to use a Unicode string?
on 10/25/2012 at 6:17 PM
This is truly a poor design decision.
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!
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?
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.
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.
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.
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.
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!
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.
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.
to post a workaround.
Please enter a workaround.
on 11/8/2012 at 5:06 PM
Scratch that, it seems the Advanced Editor reports a truncation error if you try to force non-unicode strings. Seems like a bug to me.
on 11/8/2012 at 4:42 PM
I just used the advanced editor to force it to use a normal string.
© 2013 Microsoft