How to change Zeros ( 0 ) to NULL in SSIS?

Question

I would like to change some Zeros into NULL using SSIS 2008. In that context I tried "conditional split" where I have set the columns test in question with the following expression:

REPLACE((DT_WSTR,8)test,"0", NULL(DT_STR,10,1252))

but that doesn't seem right. The expression is red.

I get the errors:

data types "DT_WSTR and "DT_I8" are incompatible for conditional operator...

Error at create lists [Derived Column 9 [141128]]: Attempt to find the input column named "NULL" failed with error code 0xC0010009. The input column specified was not found in the input column collection.

Error at create lists [Derived Column 9 [141128]]: Attempt to parse the expression "REPLACE(PLZ,"0",NULL)" failed and returned error code 0xC00470A2. The expression cannot be parsed. It might contain invalid elements or it might not be well-formed. There may also be an out-of-memory error.

Error at create lists [Derived Column 9 [141168]]: Attempt to parse the expression "[PLZ]==0 ? NULL(DT_I8, 10) : [PLZ]" failed. The expression might contain an invalid token, an incomplete token, or an invalid element. It might not be well-formed, or might be missing part of a required element such as a parenthesis.

Error at create lists [Derived Column 9 [141168]]: Cannot parse the expression "[PLZ]==0 ? NULL(DT_I8, 15) : [PLZ]". The expression was not valid, or there is an out-of-memory error

Sample data:

test     test ideal

 0            NULL
120           120
304           304
100           100
222           222
520           520
0             NULL
0             NULL
10            10
11            11
0             NULL

Answer 1

Use Derived Column Transformation with expression [test] == 0 ? NULL(DT_I8) : [test]

Answer 2

You could use the nullif function:

select nullif(test, 0) as test from ...

It's standard SQL, but I'm not sure whether it is supported in ssis.