1

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'

Hadi
  • 36,233
  • 13
  • 65
  • 124
Dheeraj
  • 19
  • 4
  • What have you tried? Its a fairly simple case expression which the docs should be able to point you in the right direction. – Dale K Dec 15 '22 at 07:51
  • Please provide enough code so others can better understand or reproduce the problem. – Community Dec 15 '22 at 13:31

3 Answers3

0

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

RF1991
  • 2,037
  • 4
  • 8
  • 17
0

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

[inputcolumn] == "2" ? "active" : 
[inputcolumn] == "4" ? "closed" : 
[inputcolumn] == "5" ? "inactive" : 
""
Hadi
  • 36,233
  • 13
  • 65
  • 124
0

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.

Anders Ericsson
  • 104
  • 8