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»»

error in derived column transformation Expand / Collapse
Author
Message
Posted Monday, August 25, 2008 1:34 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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

expressionDT_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
Posted Monday, August 25, 2008 6:13 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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
Posted Monday, August 25, 2008 6:57 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Monday, August 25, 2008 9:58 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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

Any Body help appriciated
Post #558234
Posted Tuesday, August 26, 2008 12:42 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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
Posted Wednesday, August 27, 2008 3:15 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Wednesday, August 27, 2008 9:48 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Thursday, August 28, 2008 5:52 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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
Posted Thursday, August 28, 2008 6:07 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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
Posted Friday, August 26, 2011 11:02 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued 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 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 »

Add to briefcase 12»»

Permissions Expand / Collapse