I have the following SSIS expression, which is giving me an error but I am unable to resolve it. Any help would be appreciated.
Objective: Include new condition, if
BatchManagement!=X, set value as0.
Original Query
(DT_WSTR,20)(REPLACENULL(WMSBatchNo,0))
Modified Query
(DT_WSTR,20)(REPLACENULL(BatchManagement,"") != "X" ? 0 : REPLACENULL(WMSBatchNo,0))
Error Message:
Error at Step 1 - Fetch CRM Data and Write to File [Convert Data [34]]: The data types "DT_I4" and "DT_WSTR" are incompatible for the conditional operator. The operand types cannot be implicitly cast into compatible types for the conditional operation. To perform this operation, one or both operands need to be explicitly cast with a cast operator.
Error at Step 1 - Fetch CRM Data and Write to File [Convert Data [34]]: Attempt to set the result type of conditional operation "REPLACENULL(BatchManagement,"") != "X" ? 0 : REPLACENULL(WMSBatchNo,0)" failed with error code 0xC004709F.
Error at Step 1 - Fetch CRM Data and Write to File [Convert Data [34]]: Computing the expression "(DT_WSTR,20)(REPLACENULL(BatchManagement,"") != "X" ? 0 : REPLACENULL(WMSBatchNo,0))" failed with error code 0xC00470A0. The expression may have errors, such as divide by zero, that cannot be detected at parse time, or there may be an out-of-memory error.
Error at Step 1 - Fetch CRM Data and Write to File [Convert Data [34]]: The expression "(DT_WSTR,20)(REPLACENULL(BatchManagement,"") != "X" ? 0 : REPLACENULL(WMSBatchNo,0))" on "Convert Data.Outputs.Columns[WMSBatchNumber]" is not valid.
Error at Step 1 - Fetch CRM Data and Write to File [Convert Data [34]]: Failed to set property "Expression" on "Convert Data.Outputs.Columns[WMSBatchNumber]".