I face a type cast issue occuring inside "derived column" transformation.
Following is the error msg get:
[Derived Column ] Error: The "component "Derived Column" (34)" failed because error code 0xC0049064 occurred, and the error row disposition on "output column "new amt" (173)" specifies failure on error. An error occurred on the specified object of the specified component.
That's lovely and non-specific. Sigh. Google-Fu didn't help me either. ;-)
The flat file is fixed width delimiter.
Heheh, that's kind of an oxymoron, but I get where you're going. Usually we just say 'fixed width'. I had to come back and re-read that after your further explanation. My brain only remembered the 'delimited' part of that comment.
to achieve this, i add a new column 'new amt' in it, which has the following expression in it - (DT_DECIMAL,10)(sign + amt)/100
This derived column is of datatype decimal[DT_DECIMAL]
What I usually do in these cases is try to figure out why some of my rows are failing. The easiest way to do that is to create a second data-target (I usually use Recordset Destination so it dies when I restart the process and don't have file everywhere), and redirect my error rows, using a data viewer on the redirection to see what failed. At least then I can see the data that's causing the fail and work from there.
My guess would either be strange spacing in the fixed width field, something larger then expected, or an odd character in the sign position.
If that doesn't help, can you please copy and paste the expression, a few rows from your base file, and your fixed width file definition you created in the datasource?
- Craig Farrell
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake. For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally TablesTwitter: @AnyWayDBA