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.

What happens when you put (DT_DECIMAL, 2) in front of each LateDays instance? — Eric Hauenstein
– Eric Hauenstein, Commented Feb 16, 2018 at 18:30
If I put decimal without changing the " " to a simple 0 it gives an error, because having the "" male the wole thing a string. — Rachel
– Rachel, Commented Feb 20, 2018 at 11:44

Hadi · Accepted Answer · 2018-02-17 21:29:03Z

1

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

edited Feb 17, 2018 at 21:29

answered Feb 17, 2018 at 21:22

Hadi

37.5k13 gold badges73 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Peter Mortensen · Accepted Answer · 2018-02-20 22:54:31Z

1

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.

edited Feb 20, 2018 at 22:54

Peter Mortensen

31.4k22 gold badges110 silver badges134 bronze badges

answered Feb 16, 2018 at 18:32

KeithL

5,6193 gold badges23 silver badges30 bronze badges

Comments

Peter Mortensen · Accepted Answer · 2018-02-20 22:55:03Z

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")

edited Feb 20, 2018 at 22:55

Peter Mortensen

31.4k22 gold badges110 silver badges134 bronze badges

answered Feb 18, 2018 at 5:56

Rachel

2282 gold badges5 silver badges19 bronze badges

1 Comment

Yahfoufi Over a year ago

You have to accept this answer, even if it is yours. Also if other answer was helpful, it is good to up vote them.

Collectives™ on Stack Overflow

Data conversion using SSIS

3 Answers 3

The Issue

Other Suggestion

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

The Issue

Other Suggestion

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related