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


error in derived column transformation


error in derived column transformation

Author
Message
vasu.ssis
vasu.ssis
Mr or Mrs. 500
Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)

Group: General Forum Members
Points: 577 Visits: 90
Hi guys,

i am getting so many issues to convert datatype DT_WSTR to DT_I8 in derived column Transformation.i have OLE DB source with DT_WSTR data type and i want integer in OLE DB destination.for that i use derived column transformation i wrote the expression like this

expressionSadDT_I8)(StateNo)

but it didnt convert it show below error

[Derived Column [1621]] Error: An error occurred while attempting to perform a type cast.
[Derived Column [1621]] Error: The "component "Derived Column" (1621)" failed because error code 0xC0049064 occurred, and the error row disposition on "input column "StateNo " (16034)" 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" (1621) 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.

Note: previously i did it another file it worked but right now its not working what is the problem
nkm129
nkm129
SSC Eights!
SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)

Group: General Forum Members
Points: 817 Visits: 249
use data conversion transformation component insted of dervied column..

Thanks!
vasu.ssis
vasu.ssis
Mr or Mrs. 500
Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)

Group: General Forum Members
Points: 577 Visits: 90
Hi

i used data conversion transformation instead of derived column but it getting below error

Error:The Conversion returned status value 2 and status text "the value could not be converted because of a potential loss of data."
vasu.ssis
vasu.ssis
Mr or Mrs. 500
Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)

Group: General Forum Members
Points: 577 Visits: 90
Hi

Any Body help appriciated
nkm129
nkm129
SSC Eights!
SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)

Group: General Forum Members
Points: 817 Visits: 249
Could you provide some sample data .. It might help to understand problem in better way...

Thanks!
vasu.ssis
vasu.ssis
Mr or Mrs. 500
Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)

Group: General Forum Members
Points: 577 Visits: 90
sorry for the late response

this is my sample data

Customer Key Customer no cust dt
------------ ----------- -------
50071000000000000 6328800 20070925
50070300000000000 6328800 20071105
50070900000000000 6328820 00000000
50070300000000000 6328820 20070507
50080500000000000 6328830 20080314
50071100000000000 6328840 00000000
50070300000000000 6328840 20080527
50071100000000000 6328860 19790523
50070500000000000 6328860 19900925
50070300000000000 6328870 00000000

thanks
vasu
vasu.ssis
vasu.ssis
Mr or Mrs. 500
Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)

Group: General Forum Members
Points: 577 Visits: 90
above sample data

convert customer key into integer
convert custdt into date

any help appriciated

thanks
vasu
nkm129
nkm129
SSC Eights!
SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)

Group: General Forum Members
Points: 817 Visits: 249
Hi,

Issue with date column... use DT_STR and then in derived column use substring to include // so date will become 2007/04/05 and then transfer data to destination...

it will work out..

Thanks!
Naveen
nkm129
nkm129
SSC Eights!
SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)

Group: General Forum Members
Points: 817 Visits: 249
Hi,

Here is solution:-

1) Source :- Flat file use DT_STR for Date column
2) Derived Column :- Replace this with using

SUBSTRING([Column 2],4,4) + "/" + SUBSTRING([Column 2],5,2) + "/" + SUBSTRING([Column 2],7,2)

and use DT_DATE IN Datatype

3) Destination :- Use datetime as datatype for date

Run it

Thanks!
durga palepu
durga palepu
SSC Veteran
SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)

Group: General Forum Members
Points: 223 Visits: 46
Hi,
I know its too late response:-) But I have seen this post for the first time.
What I understood is that he wish to convert customer no to INT, in derived column instead of using replace column option to convert data type, use <add column> and then apply type cast DT_I8.

I hope this will help you.
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