Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

convert datatype using derived column transformation Expand / Collapse
Author
Message
Posted Thursday, August 14, 2008 4:59 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, December 19, 2008 9:30 AM
Points: 23, Visits: 90
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
Post #552564
Posted Thursday, August 14, 2008 5:55 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 4:04 AM
Points: 1,865, Visits: 591
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
Post #552591
Posted Thursday, August 14, 2008 6:22 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, December 19, 2008 9:30 AM
Points: 23, Visits: 90
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

Post #552605
Posted Thursday, August 14, 2008 6:52 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 4:04 AM
Points: 1,865, Visits: 591
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
Post #552629
Posted Thursday, August 14, 2008 8:23 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, July 14, 2014 3:32 PM
Points: 917, Visits: 412
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,
Crispin


I can't die, there are too many people who still have to meet me!

It's not a bug, SQL just misunderstood me!
Post #552746
Posted Monday, August 18, 2008 12:14 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, December 19, 2008 9:30 AM
Points: 23, Visits: 90
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.
Post #554103
Posted Monday, August 18, 2008 2:07 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, December 19, 2008 9:30 AM
Points: 23, Visits: 90
sorry for the inconvience plz reply its urget
Post #554146
Posted Monday, August 18, 2008 2:10 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 4:04 AM
Points: 1,865, Visits: 591
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
Post #554147
Posted Monday, August 18, 2008 4:17 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, December 19, 2008 9:30 AM
Points: 23, Visits: 90
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

Post #554189
Posted Monday, August 18, 2008 4:52 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 4:04 AM
Points: 1,865, Visits: 591
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
Post #554202
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse