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