error in derived column transformation

Question

Post reply

error in derived column transformation

vasu.ssis

Default port

Points: 1423
More actions
August 25, 2008 at 1:34 am

#154653

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
expression:(DT_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

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

nkm129 SSCommitted Points: 1763 More actions · Answer 1

use data conversion transformation component insted of dervied column..

Thanks!

vasu.ssis Default port Points: 1423 More actions · Answer 2

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 Default port Points: 1423 More actions · Answer 3

vasu.ssis

Default port

Points: 1423

August 25, 2008 at 9:58 am

#862610

Hi

Any Body help appriciated

nkm129 SSCommitted Points: 1763 More actions · Answer 4

Could you provide some sample data .. It might help to understand problem in better way...

Thanks!

vasu.ssis Default port Points: 1423 More actions · Answer 5

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 Default port Points: 1423 More actions · Answer 6

above sample data

convert customer key into integer

convert custdt into date

any help appriciated

thanks

vasu

nkm129 SSCommitted Points: 1763 More actions · Answer 7

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 SSCommitted Points: 1763 More actions · Answer 8

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 Say Hey Kid Points: 701 More actions · Answer 9

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.

sudhakara SSCrazy Eights Points: 9425 More actions · Answer 10

[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Source - xxxxxtablename" (1) returned error code 0xC0202009.

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. There may be error messages posted before this with more information about the failure.

I too had similar problem when i import data from MS Access to sql server . I copied the MS Access database into the local drive from share path and then i execute the package , it had completed successfully and fast too.