0

what is the SSIS equivalent expression(Derived Column component) of the below SQL expression

cast(CASE WHEN len(cast(KPI as varchar(3))) > 2 THEN 
        CASE substring(cast(KPI as varchar(3)),3,1)
            WHEN 1 then left(cast(KPI as varchar(3)),1) + 'a'
            WHEN 2 then left(cast(KPI as varchar(3)),1) + 'b'
            WHEN 3 then left(cast(KPI as varchar(3)),1) + 'c'
            WHEN 4 then left(cast(KPI as varchar(3)),1) + 'd'
        END
        ELSE cast(KPI as varchar(3))
    END as VarChar(3)) as 'ColumnName'

here Kpi column is a double precision floating point data type...

one major thing i have observed here is LEFT String function is missing from SSIS Expression Builder.

SSIS experts please have a look..

user1254579
  • 3,901
  • 21
  • 65
  • 104
  • 1
    what you meen SSIS expression? In which element you want use this code? This code you can convert to SSIS derived column code, but then that's the point, if you can just use in Database Source element this code... – Justin Mar 05 '14 at 13:28
  • I need to use this in DERIVED COLUMN COMPONENT – user1254579 Mar 05 '14 at 13:29
  • 2
    I don't suggest you to use this on derived column. Why can't you do this one in the source query. – Maverick Mar 05 '14 at 14:48

1 Answers1

0

I have been trying around 1 hour on my system..finally found the answer..Hope it would be useful to somebody else

created a new column,NewKPI and converted that to dt_str in dataconversion componet (as mentioned by Justin )

(DT_STR,3,1252)(LEN(NewKPI) <= 2 ? NewKPI : (SUBSTRING(NewKPI,3,1) == "1") ? "2A" : (SUBSTRING(NewKPI,3,1) == "2") ? "2b" : (SUBSTRING(NewKPI,3,1) == "3") ? "2c" : (SUBSTRING(NewKPI,3,1) == "4") ? "2d" : NULL(DT_WSTR,3))

(I have used substring instead of Left)

Thanks all for the help

user1254579
  • 3,901
  • 21
  • 65
  • 104