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.sql
No comments:
Post a Comment