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?

Here is the Formula

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

  • 1
    Have you tried changing it to (DT_STR,20,1252)((IPUNSPSC) =="no_UNSPSC_available" ? NULL(DT_WSTR,20) : TRIM(IPUNSPSC))?
    – RDFozz
    Jul 5, 2017 at 18:00

1 Answer 1


It's as @RDFozz identified in their comment.

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

Derived Column As String

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">
        <OleDbConnection Name="SRC" ConnectionString="Provider=SQLNCLI11;Server=localhost\dev2016;Initial Catalog=tempdb;Integrated Security=SSPI;" />
        <Package Name="dba_178090">
                <Dataflow Name="DFT Demo">
                        <OleDbSource Name="OLESRC Query" ConnectionName="SRC">
                            <DirectInput>SELECT '**no_UNSPSC_available**' AS IPUNSPSC UNION ALL  SELECT '***********************';</DirectInput>
                        <DerivedColumns Name="DER Demo">
                                <Column Name="Derived" DataType="AnsiString" Length="20" CodePage="1252">(DT_STR,20,1252)((IPUNSPSC) =="no_UNSPSC_available" ? NULL(DT_WSTR,20) : TRIM(IPUNSPSC))</Column>
                        <!-- Placeholder transformation -->
                        <DerivedColumns Name="DER Bit Bucket" />

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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