SSIS Derived Column Expression from SQL code CASE..WHEN

Question

I have been trying to convert the following SQL code with CASE--WHEN to SSIS Derived Column expression. The expression would go in a new column with varchar(5) datatype.

The SQL Code:

CASE
WHEN SUBSTRING(A.Column1,1,5) = 'John'
    OR 
    (C.Column1 IS NOT NULL
    AND A.Column2 = 'Two')
        THEN 'Two'  
WHEN SUBSTRING(A.Column1,1,5) = 'Mike'  
        THEN 'HD' 
WHEN D.Column1 IS NOT NULL
    AND F.Column1 IS NOT NULL
    AND G.Column1 LIKE '%DDD%'
    AND G.Column2 = H.Column1 
    AND H.Column2 = '1'
    AND H.Column3 = 'Y'
    AND H.Column4 <= A.Column3
    AND H.Column5 > A.Column3
    AND H.Column6 <= A.Column3
        THEN 'HE'  
WHEN B.Column1 IS NOT NULL
        THEN '' 
WHEN E.Column1 IS NOT NULL 
        THEN '22' 
ELSE ''

The expression I have:

SUBSTRING([A.Column1],1,5) == "John" 
|| (ISNULL([C.Column1]) == FALSE 
&& ([A.Column2] == "Two") ? "Two":

(SUBSTRING([A.Column1],1,5) == "Mike" ? "HD":
(ISNULL([D.Column1]) == FALSE
    && ISNULL([F.Column1]) == FALSE
    && FINDSTRING([G.Column1], "DDD",1) > 0
    && [G.Column2] ==[H.Column1] 
    && [H.Column2] == "1"
    && [H.Column3] == "Y"
    && [H.Column4] <= A.Column3
    && [H.Column5] > A.Column3
    && [H.Column6] <= A.Column3 ? "HE":
(ISNULL([B.Column1]) == FALSE ? "":
(ISNULL([E.Column1]) == FALSE ? "22":""
))))

I am new to building expressions, and haven't been able to find out where I am going wrong. I cannot download the Expression Tester software because I cannot run .exe on work computer. Any help greatly appreciated.

score 0 · Accepted Answer · answered Jul 10 '14 at 16:10

You are missing a closing bracket before ? "Two":

Try this:

SUBSTRING([A.Column1],1,5) == "John" 
|| (ISNULL([C.Column1]) == FALSE 
&& ([A.Column2] == "Two")) ? "Two":    
(SUBSTRING([A.Column1],1,5) == "Mike" ? "HD":
(ISNULL([D.Column1]) == FALSE
    && ISNULL([F.Column1]) == FALSE
    && FINDSTRING([G.Column1], "DDD",1) > 0
    && [G.Column2] ==[H.Column1] 
    && [H.Column2] == "1"
    && [H.Column3] == "Y"
    && [H.Column4] <= A.Column3
    && [H.Column5] > A.Column3
    && [H.Column6] <= A.Column3 ? "HE":
(ISNULL([B.Column1]) == FALSE ? "":
(ISNULL([E.Column1]) == FALSE ? "22":""
))))

Thank you for catching that! Other than that, my H.Column2 was numeric datatype. So, I also had to cast the datatype as such: `[H.Column2] == (DT_NUMERIC, 1, 0)1` — Meta747, Jul 10 '14 at 19:56

SSIS Derived Column Expression from SQL code CASE..WHEN

1 Answers1