SSIS – How to Change Zeros (0) to NULL

ssis

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

Best Answer

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

Related Question