Derived Column error:can''t change datatype

I am trying to replace the value of a column in a derived column component, but it will not let me change the datatype.

It has decided that the column is a float, which is wrong.

How can I change it to the correct type?

Adding a cast function to the expression of the derived column should work for you. You could also use a data transformation component, but that would add extra columns to the pipeline.|||

Unfortunately this doesn't work.

The following

ISNULL(FaceValue) ? (DT_NUMERIC,28,9)(NotionalAmount + ReturnGrossAmount) : (DT_NUMERIC,28,9)FaceValue

gives a datatype of

double-precision float

even changing it to

(DT_NUMERIC,28,9)(ISNULL(FaceValue) ? (DT_NUMERIC,28,9)(NotionalAmount + ReturnGrossAmount) : (DT_NUMERIC,28,9)FaceValue)

makes no difference

|||That's interesting. I just put the same expression into a Derived Column transform, and it set the data type to DT_NUMERIC(28,9). I'm on SP2 - what SP are you running?|||win xp pro: v5.1 (build 2600.xpsp_sp2_gdr.050301-1519 : Service Pack 2)

Are you sure you are setting "replace column X" and not "add as new column"?|||workaround:

on the oledb source, advanced editor, set the datatype on the output column to numeric, then it is already correct on the way in

yet another hacky timewaster methinks...|||

Pipeline buffer columns' data types are fixed.

So, in a derived column, when using 'Replace' mode , that means one can change the contents alone, not the data type. This fixed typing is why the data conversion component always adds a new pipeline column, for example.

SSIS pipeline metdata is fixed, or deterministicly typed. Deterministic pipeline metdata is a design feature of SSIS, one you may not agree with.

|||I've said before that you should be able to operate SSIS in a given 'data mode' i.e. sql server.

The least it should do is correctly map the datatypes based on the connection manager type/subtype according to the recommendations in BOL

My Numeric column should map to DT_NUMERIC, why on earth it is choosing to map to DT_R8 is beyond me.