I am using a flat file source in which some rows may have an empty string for a position which I would like to cast to a null. Each row in the ff has a differenct structure so I need to use substring to get the position I want to look at. I am using this expression but the result of this expression is a unicode ST_WSTR and I want it to be a string DT_STR. This is my expression: SUBSTRING(AllColumns,56,22) == "" ? (DT_STR,22,1252)NULL(DT_STR,22,1252) : (DT_STR,22,1252)SUBSTRING(AllColumns,56,22) This results in a unicode value so I tried to cast it back to a DT_STR: (DT_STR,22,1252)(SUBSTRING(AllColumns,56,22) == "" ? (DT_STR,22,1252)NULL(DT_STR,22,1252) : (DT_STR,22,1252)SUBSTRING(AllColumns,56,22)) This results in a ST_STR value but I end up with empty strings in the database fields again.

Then remove the empty strings with REPLACE(AllColumns, " ", "")Arthur My Blog

Then remove the empty strings with REPLACE(AllColumns, " ", "") Arthur My Blog Thanks for the suggestion. If I use another deirived column between the ff and the current derived column and use that expression then some columns get shifted so the substrings don't extract the correct data in the current derived column.

I think the lesson is probably you need not to convert to non-unicode, keep processing Unicode data and then convert if needed as the last step Arthur My Blog

Turns out the reason the empty string was getting inserted was because SUBSTRING(AllColumns,56,22) == "" was always false since the substring would enter 22 spaces and so it was never empty. The solution was to trim the substring result. I casted it all back to a DT_STR,22,1252 to get back to the non-unicode string in the end. (DT_STR,22,1252)(TRIM(SUBSTRING(AllColumns,56,22)) == "" ? (DT_STR,22,1252)NULL(DT_STR,22,1252) : (DT_STR,22,1252)SUBSTRING(AllColumns,56,22))