Replace "-" with NULL with Derived Column in SSIS I am extracting information from an excel sheet and loading it into a database. Before I load it into the database, I must transform all of the "-" in the excel sheet to NULL values. 

I am currently trying to do this with the derived column transformation in a data flow object. It works if the data type is a Unicode string [DT_WSTR], however it will not work with a double-precision float [DT_R8].

The expression that works for the unicode strings is below.

[EquipmentID]  == "-" ? (DT_WSTR,50)NULL(DT_WSTR,50) : [EquipmentID]


The expression that is not working for the double-precision floats is below.

[SideDoorType] == "-" ? (DT_R8)NULL(DT_R8) : [SideDoorType]
First of all, there is no need to cast NULL() function (DT_R8)NULL(DT_R8) you can use it without casting. Only you will need to cast when working with DT_STR. Read more at:


https://docs.microsoft.com/en-us/sql/integration-services/expressions/null-ssis-expression
https://stackoverflow.com/questions/43455146/why-does-this-ssis-conversion-expression-syntax-the-way-it-is


The problem is that you are using a Case statement that returns 2 datatypes

[SideDoorType] == "-" ? NULL(DT_R8) : [SideDoorType]


You are comparing [SideDoorType] column to a string value "-",
if it is equal, you are trying to return a double-precision floats NULL , else you are returning [SideDoorType] column.

you can try using the following expression it might be working

if [SideDoorType] type is DT_WSTR or DT_STR, you have to cast the column in the false part expression

[SideDoorType] == "-" ? NULL(DT_R8) : (DT_R8)[SideDoorType]


if  [SideDoorType] type is DT_R8, you have to cast the column in the comparaison expression

 (DT_WSTR,50,1256)[SideDoorType] == "-" ? NULL(DT_R8) : [SideDoorType]


And if these two expressions are not working, you have to use a string dataType like the first expression you provided, because it looks like your column contains values that cannot be converted to DT_R8, or use a Script Component instead of Derived Column and write a script to clean your data before casting it to float