Derived Column return WRONG datatype.

Hi, friend,
l've 2 variables A & B from source database which i used to calculate C with the following formula, i.e.

C = 100 * A/B

In case of B = 0 (DIV BY ZERO), C should be equal to -9999.

l've set the datatype for A & B are INT, C is FLOAT at targetting database.

So l used to derived column to calculate C as,
B == 0 ? -9999 : 100 * A/B

After l run the package, l realize that all my C is INTEGER rather than FLOAT.....it seems that SQL server has evaluate the wrong datatype for me....anyway to overcome this?

This should not be a problem - you can map an integer in the data flow to a float at the destination without any issues.

Donald Farmer

|||Fact is, l get a Integer at the end :(, even the data and meta data is float........
|||

Try casting A and B as floats before using them in your calculation.

-Jamie

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.

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.sql

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.

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.

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.

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.

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.sql

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.