CODEWITHSUNDEEP
ssis
user3772443
user3772443

Reputation: 159

SSIS Derived Column Expression - removing leading zeros

I have a package that is failing due to a negative sign with a numerical string such as:

000000000-25.00

The Derived column expression is: 

ISNULL(wstr_Payment_Amount) || TRIM(wstr_Payment_Amount) == "" ? (DT_CY)0 : (DT_CY)wstr_Payment_Amount

The data type to column is money. I did a redirect row on the derived column to confirm it was the negative sign that was causing the failure.

Here is what I have.. 

FINDSTRING(wstr_Payment_Amount, "-", 1) >0  ? TRIM(SUBSTRING(wstr_Payment_Amount, FINDSTRING(wstr_Payment_Amount, "0-", 1), 8)) : wstr_Payment_Amount

I am using SSIS 2008.

Upvotes: 1

Views: 714

Answers (1)

KeithL
KeithL

Reputation: 5594

If you are using two derived columns then leave it as a string in the first one...

ISNULL(wstr_Payment_Amount) || TRIM(wstr_Payment_Amount) == "" ? (DT_WSTR,100)"0" : (DT_WSTR,100)wstr_Payment_Amount

I think you were really close but consider the following once you identify the negative sign...

FINDSTRING(wstr_Payment_Amount, "-", 1) >0  ? (DT_CY) ("-" + replace(wstr_Payment_Amount,"-","")) : (DT_CY)wstr_Payment_Amount

This will just move the "-" to the front and all the zeroes will just go away.

Upvotes: 2

Related Questions