How to Resolve Conditional Operation Failed Error in SSIS Derived Column Transformation?

Question

I'm attempting to read a flat file containing all string values in a pipe "||" delimited format. Some of the fields are null and I receive the above error in my Derived Column Transformation when evaluating the following expression:

ISNULL(ExternalUsedDeductibleAmount) ==  TRUE  ? 0.00 : (REPLACENULL((DT_DECIMAL,2)
(DT_R4)ExternalUsedDeductibleAmount,0.00))

Since the flat file may or may not contain a value in this field, I want to add the value 0.00 if the field is null. I originally tried using just REPLACENULL but have since reviewed guidance that I needed to evaluate the field for Null values as part of the expression.

How do I rewrite the expression such that the Derived Column transformation passes a default value of 0.00 when the source data column is null?

Answer 1

Your condition in the derived column should be like below :

(ExternalUsedDeductibleAmount == "") ? "0.00" : ExternalUsedDeductibleAmount

Keep in mind that you need to convert the column ExternalUsedDeductibleAmount to the appropriate data type of your destination (using the Data Conversion component)