convert datatype using derived column transformation
-
Hi guys
In my OLEDB source i have one column customer no, it is string but my requirement is convert into integer or any numeric.for this i use data conversion transformation but it insert another column(like in output alias) but i want change same column dont inculde another column.my suggestion is using derived column transformation it is possible. but i dont know how to write expression in derived column transformation.give me the detailed expression how to covert.
any help appreciated.
vasu
-
in the derived column transformation, expand the list of columns in the top left pane. click on the one you want (the new one from the conversion transformation) and drag it down to the Expression box in the main editing pane. click in the derived column box and a drop down appears. select "replace '[your column]'". that's it.
btw, is it a real problem to have the extra column?
tom
Life: it twists and turns like a twisty turny thing
-
Hi hodgy
what u said is correct.it doesn't change the column.but at the same time my datatype also will be converted(from string to integer) that is my main problem.for that purpose what we have to do in expression.
thanks
vasu
-
You can do it in the derived column transformation using the syntax (DT_I4)[Your Column] in the expression field (for a four-byte signed integer).
Life: it twists and turns like a twisty turny thing
-
Why not convert it at source? Doing it in the DF is a waste of time(CPU) and memory. Unless of course, you are going to use the string value.
I am guessing you have selected the table / view as your source. Don't. Rather make it a query and select only the columns you want and do any conversions there.
You way, again assuming you don't use the string value, will create a buffer to hold the string (Memory + CPU), convert it (CPU), Copy it into the new buffer[no place conversion] (Memory + CPU). What a waste...
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
-
sorry for the late replay
hi hodgy
what u suggested i implement in the derived column (DT_14)(Customer no) at the execution time it showed following error.
[Derived Column [2149]] Error: An error occurred while attempting to perform a type cast.
[Derived Column [2149]] Error: The "component "Derived Column" (2149)" failed because error code 0xC0049064 occurred, and the error row disposition on "input column "Customer No " (3596)" specifies failure on error. An error occurred on the specified object of the specified component.
[DTS.Pipeline] Error: The ProcessInput method on component "Derived Column" (2149) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
[DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0xC0209029.
[OLE DB Source [1]] Error: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.
[DTS.Pipeline] Error: The PrimeOutput method on component "OLE DB Source" (1) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
[DTS.Pipeline] Error: Thread "SourceThread0" has exited with error code 0xC0047038.
-
sorry for the inconvience plz reply its urget
-
use the error output of the transformation to find out which rows are causing you problems.
Life: it twists and turns like a twisty turny thing
-
hi hodgy
i used error output.all rows went to error output
in error output
errorcode:-1073442796
errocolumn;11519
errordescription:an error occured during computation of the expression
tell me related solve
-
what happens if you do as Crispin suggests and carry out the conversion in the source SQL? So, choose SQL command from the drop down and cast the column to an int.
Life: it twists and turns like a twisty turny thing
-
Hi,
If anyone has used the logical and on the Derived column data flow please provide woth an eg.
Ta,
Duda
What you don't know won't hurt you but what you know will make you plan to know better -
Hi,
If anyone has done a logical and used on a Derived column data flow item please provide some code as mine is not working because of the syntax error.
Tx,
Duda
What you don't know won't hurt you but what you know will make you plan to know better -
Tx i manged to get this.
Wanted soming like :
EXPRESSION
QTY > 0 && TYPE == "ORDER" ? "O" : "I"
What you don't know won't hurt you but what you know will make you plan to know better
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply