How to convert SQL Case statement to SSIS Expression Language?

0
votes

I have a fully qulified file name (file name with path) coming into staging table in SSIS load. File name is eg.

C:\SSIS\scripts\work\input\tom_22082017-093346-906838.csv
C:\SSIS\scripts\work\input\jim_22082017-093346-906838.csv

I would like extract only the file name from this and genereate a new column using a case logic in SSIS derived column transformation. Using pure SQL I could do this like:

SELECT CASE 
        WHEN SUBSTRING(UPPER(RIGHT(filename, CHARINDEX('\', REVERSE(filename)) - 1)), 1, 3) = 'TOM' THEN 'TOMMY' 
        WHEN SUBSTRING(UPPER(RIGHT(filename, CHARINDEX('\', REVERSE(filename)) - 1)), 1, 3) = 'JIM' THEN 'JIMMY' 
    END AS PERSON
    FROM mystagingtable

How to convert this logic to SSIS Expression language in Derived Column Transformation?

2
sql-serverssisexpressionssis-2012derived-column

2 Answers

1
votes

In SSIS expressions you can use nested ternary operators.

{Condition} ? {Return value when true} : {Return value when false}
1
votes

Step 1: Used DerivedColumn to create a new column that contains the 3char name (tom, jim etc) from the filename. The SQL functions you're using (substring, upper etc) have equivalent functions within the SSIS script editor.

Step 2: Use another DerivedColumn to generate a new column which converts the above column (tom, jim etc) to the full name (tommy, jimmy). Use the ternary operator (see Tab Alleman answer below).