3

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
3

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

| improve this answer | |
1

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.

| improve this answer | |
  • 1
    It is supported if the data source supports it. Not as an SSIS component but in the source query – Tom V Jul 5 '16 at 18:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.