error in derived column transformation

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Integration Services » error in derived column transformation

11 posts, Page 1 of 2

1 2 »»

error in derived column transformation

Rate Topic

Display Mode

Topic Options

Author

Message

vasu.ssis

Posted Monday, August 25, 2008 1:34 AM

Grasshopper

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

expression Sad

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

Post #557982

nkm129

Posted Monday, August 25, 2008 6:13 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, July 31, 2010 11:58 AM
Points: 147, Visits: 249

use data conversion transformation component insted of dervied column..

Thanks!

Post #558081

vasu.ssis

Posted Monday, August 25, 2008 6:57 AM

Grasshopper

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

Post #558096

vasu.ssis

Posted Monday, August 25, 2008 9:58 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, December 19, 2008 9:30 AM
Points: 23, Visits: 90

Hi

Any Body help appriciated

Post #558234

nkm129

Posted Tuesday, August 26, 2008 12:42 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, July 31, 2010 11:58 AM
Points: 147, Visits: 249

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

Thanks!

Post #558625

vasu.ssis

Posted Wednesday, August 27, 2008 3:15 AM

Grasshopper

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

Post #559445

vasu.ssis

Posted Wednesday, August 27, 2008 9:48 PM

Grasshopper

Group: General Forum Members
Last Login: Friday, December 19, 2008 9:30 AM
Points: 23, Visits: 90

above sample data

convert customer key into integer
convert custdt into date

any help appriciated

thanks
vasu

Post #560103

nkm129

Posted Thursday, August 28, 2008 5:52 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, July 31, 2010 11:58 AM
Points: 147, 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

Post #560313

nkm129

Posted Thursday, August 28, 2008 6:07 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, July 31, 2010 11:58 AM
Points: 147, 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!

Post #560321

durga palepu

Posted Friday, August 26, 2011 11:02 AM

Valued Member

Group: General Forum Members
Last Login: Tuesday, September 06, 2011 10:10 AM
Points: 57, Visits: 46

Hi,
I know its too late response Smile

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.

Post #1166284

« Prev Topic | Next Topic »

11 posts, Page 1 of 2

1 2 »»

Permissions