SSIS Derived Column Casting to DT_STR

Question

Why can't I get this to cast as a DT_STR - the data source is WSTR, but shouldn't this cast make the output DT_STR?

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

Answer 1

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.

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