ssis case statement derived column

still learning about SSIS

I have an excel source in SSIS, I need to create a case statement based on one of the columns [Teacher]

but as a derived column (as it does not work in the SQL statement)

the normal SQL would be

CASE WHEN [Teacher] like '%A%' then [Teacher] else null end as [Teacher]

​x
 
CASE WHEN [Teacher] like '%A%' then [Teacher] else null end as [Teacher]​

I have tried to look for examples, but can’t find any with use of the like ‘%A%’

please help

Answer

You should use conditional with FINDSTRING() function as following (assuming that [Teacher] column is of type DT_WSTR):

FINDSTRING([Teacher],"A",1) > 0 ? [Teacher] : NULL(DT_WSTR,50)

 
FINDSTRING([Teacher],"A",1) > 0 ? [Teacher] : NULL(DT_WSTR,50)​