SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


convert datatype using derived column transformation


convert datatype using derived column transformation

Author
Message
vasu.ssis
vasu.ssis
SSC-Enthusiastic
SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)

Group: General Forum Members
Points: 115 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
hodgy
hodgy
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2037 Visits: 596
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

vasu.ssis
vasu.ssis
SSC-Enthusiastic
SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)

Group: General Forum Members
Points: 115 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
hodgy
hodgy
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2037 Visits: 596
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

Crispin Proctor
Crispin Proctor
UDP Broadcaster
UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)

Group: General Forum Members
Points: 1439 Visits: 414
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!
vasu.ssis
vasu.ssis
SSC-Enthusiastic
SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)

Group: General Forum Members
Points: 115 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.
vasu.ssis
vasu.ssis
SSC-Enthusiastic
SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)

Group: General Forum Members
Points: 115 Visits: 90
sorry for the inconvience plz reply its urget
hodgy
hodgy
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2037 Visits: 596
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

vasu.ssis
vasu.ssis
SSC-Enthusiastic
SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)

Group: General Forum Members
Points: 115 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
hodgy
hodgy
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2037 Visits: 596
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

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search