ssis case statement derived column

Question

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]

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

please help

Since you're learning, what were some of the search terms you were using? — billinkc
search google, it's actually better than searching stackoverflow for stackoverflow results: ssis derived column using like. First result is exactly what you need: stackoverflow.com/questions/4739230/… — KeithL

Hadi Hadi · Accepted Answer · 2020-08-12T00:01:57

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)