Data conversion using SSIS

Question

I have the following expression that works perfectly,

ISNULL(LateDays < 0 ? (DT_WSTR,30)OriginalTransactionAmount : "") ? "0"
: (LateDays < 0 ? (DT_WSTR,30)OriginalTransactionAmount : "")

The issue is that OriginalTransactionAmount is decimal, and I want the whole expression to be decimal. LateDats is (DT_14) integer. When I try to replace the DT_WSTR it gives me an error. Any clue on how I can make it decimal?

I am using this data in a report, and it's giving me a hard time, because the reporting tool is reading it as a text.

Answer 1

I replaced the " " with "0". Then after that added a data conversion task and changed it to decimal and it works.

ISNULL(LateDays < 0 ? (DT_WSTR,30)OriginalTransactionAmount : "0") ? "0" 
: (LateDays < 0 ? (DT_WSTR,30)OriginalTransactionAmount : "0")

Answer 2

Break it into pieces:

isnull(LateDays) ? (DT_NUMERIC,18,2) 0 :

LateDays < 0 ? (DT_NUMERIC,18,2) OriginalTransactionAmount : (DT_NUMERIC,18,2) 0

LateDays is in the Boolean portion and is not a result. All results have to be the same data type.

Answer 3

The Issue

The issue cause is that when using ? : conditional operators, both false and true result must have the same data type. So when using LateDays < 0 ? (DT_NUMERIC,18,2)OriginalTransactionAmount : "" there are one Decimal result and one String result, you can use the following expression instead of this *(replace "" with NULL(DT_NUMERIC):

ISNULL(LateDays < 0 ? (DT_NUMERIC,18,2)OriginalTransactionAmount : NULL(DT_NUMERIC,18,2)) ? (DT_NUMERIC,18,2)0 
: (LateDays < 0 ? (DT_NUMERIC,18,2)OriginalTransactionAmount : NULL(DT_NUMERIC,18,2))

Other Suggestion

Try using REPLACENULL() function

(REPLACENULL(LateDays,0) < 0) ? (DT_NUMERIC,18,2)OriginalTransactionAmount : (DT_NUMERIC,18,2) 0