Case expressions in derived column for SSIS

Question

How do I modify the column having 2,4,5 as values to 2=active, 4=closd, and 5=inactv in SSIS derived column? (SQL Server DB)

I'm expecting the column should show values.

'active' instead of '2'
'closd' instead of '4'
'inactv' instead of '5'

Answer 1

your data

declare @a table( c varchar(100))
insert into @a
(c) values 
('2,4,5');
declare @equivalenttable table( id varchar(100),equivalent  varchar(100) )
insert into @equivalenttable
(id,equivalent) values 
('2' ,'active'),
('4' ,'closd'),
('5' ,'inactv');

first use string_split and Cross apply for split string, second use join with equivalent table and third use string_agg as follows:

select 
  string_agg(equivalent, ',') within group (
    order by 
      id
  ) c 
from 
  (
    select 
      a1.value 
    from 
      @a CROSS APPLY STRING_SPLIT(c, ',') a1
  ) a2 
  join @equivalenttable e on a2.value = e.id

dbfiddle

Answer 2

You should use the conditional operator (?:) as follows:

[inputcolumn] == "2" ? "active" : 
[inputcolumn] == "4" ? "closed" : 
[inputcolumn] == "5" ? "inactive" : 
""

Answer 3

My suggestion is to stay away from derived columns and implement the case statement in the database query. Firstly it offloads the execution to the database. Secondly, derived columns are not that easy to work with and we want to keep the number of derived columns (or any number of ssis artifacts for that matter) as low as possible.