How to set empty a space from a derived column expression?

hi,

This field comes from a flat file and sometimes own zero and sometimes own a empty position (after 'EUR'):

2006053000499236000005307700108287457080200408287452006052953990000000010000EUR
2006053000499236004414989200101423426004400501423422006052953990000000010000EUR0

[Column 12] == "0" ? [Column 12] : ?

TIA

What is the data type of [Column 12]?

-Jamie

|||

At destination?

[char](1) COLLATE Modern_Spanish_CI_AS NULL,

|||

No. In the pipeline.

If it is DT_STR then your expression will be:

[Column 12] == "0" ? [Column 12] : NULL(DT_STR,1,1252)

If it is DT_WSTR then it'll be

[Column 12] == "0" ? [Column 12] : NULL(DT_WSTR,1)

-Jamie

|||

I've got string [DT_STR] as datatype in my flat file definition and string [DT_WSTR] as datatype column on the right 'Expression' field (Derived Column)

I've allocated as you said this line:

[Column 12] == "0" ? [Column 12] : NULL(DT_WSTR,1)

And remains in black, ok but when I launch the package appears this:

Error at Camara Recibida [OLE DB Destination [9902]]: Columns "Derived Column 1_12" and "Rein" cannot convert between unicode and non-unicode string data types.

Error at Camara Recibida [DTS.Pipeline]: "component "OLE DB Destination" (9902)" failed validation and returned validation status "VS_ISBROKEN".

Error at Camara Recibida [DTS.Pipeline]: One or more component failed validation.

Error at Camara Recibida: There were errors during task validation.

|||

So you are inputting a DT_STR but outputting a DT_WSTR? Is that correct? That seems like a strange thing to do to me but of course, it is entirely up to you. If it were me I would use the same type on the output as on the input - but it is your choice.

If you want to output a DT_STR then the expression should be:

[Column 12] == "0" ? [Column 12] : NULL(DT_STR,1,1252)

If you want to output a DT_WSTR then the expression should be:

[Column 12] == "0" ? (DT_WSTR, 1)[Column 12] : NULL(DT_WSTR,1)

-Jamie

P.S. Please try and include all relevant information when posting a problem. If you are trying to do some data conversion then one of the most obvious things to state is the types of the input columns and what types you require the output to have.

|||

That's fine now.

Thanks a lot indeed for your help.