SSIS Derived Column Casting to DT_STR
It's as @RDFozz identified in their comment. 

(DT_STR,20,1252)((IPUNSPSC) =="no_UNSPSC_available" ? NULL(DT_WSTR,20) : TRIM(IPUNSPSC))




The ternary operator ? : doesn't behave as one might expect. You casted the two resulting values as DT_STR but that's not where SSIS picks up the metadata. Instead, you need to cast the entire result of the ternary operator to a given type.

The following Biml generates a sample SSIS package with an OLE DB Query to present the IPUNSPSC column and then a Derived Column component to manipulate the data as expected.

&lt;Biml xmlns="http://schemas.varigence.com/biml.xsd"&gt;
    &lt;Connections&gt;
        &lt;OleDbConnection Name="SRC" ConnectionString="Provider=SQLNCLI11;Server=localhost\dev2016;Initial Catalog=tempdb;Integrated Security=SSPI;" /&gt;
    &lt;/Connections&gt;
    &lt;Packages&gt;
        &lt;Package Name="dba_178090"&gt;
            &lt;Tasks&gt;
                &lt;Dataflow Name="DFT Demo"&gt;
                    &lt;Transformations&gt;
                        &lt;OleDbSource Name="OLESRC Query" ConnectionName="SRC"&gt;
                            &lt;DirectInput&gt;SELECT '**no_UNSPSC_available**' AS IPUNSPSC UNION ALL  SELECT '***********************';&lt;/DirectInput&gt;
                        &lt;/OleDbSource&gt;
                        &lt;DerivedColumns Name="DER Demo"&gt;
                            &lt;Columns&gt;
                                &lt;Column Name="Derived" DataType="AnsiString" Length="20" CodePage="1252"&gt;(DT_STR,20,1252)((IPUNSPSC) =="no_UNSPSC_available" ? NULL(DT_WSTR,20) : TRIM(IPUNSPSC))&lt;/Column&gt;
                            &lt;/Columns&gt;
                        &lt;/DerivedColumns&gt;
                        &lt;!-- Placeholder transformation --&gt;
                        &lt;DerivedColumns Name="DER Bit Bucket" /&gt;
                    &lt;/Transformations&gt;
                &lt;/Dataflow&gt;
            &lt;/Tasks&gt;
        &lt;/Package&gt;
    &lt;/Packages&gt;
&lt;/Biml&gt;