SSIS expression need help

  • Not sure if this is good area to post this but could not find specific SSIS place for it. I have below transformation rule which I am trying to put it in derived column. Having issues with how I can set two separate case with else. please help.

    The numbers are CASE_STATUS

    1=A1021002

    2=A1021002

    3=A1021002

    4=A1021002

    5=A1021002

    6=A1021002

    7=A1021003

    8=A1021003

    10=(CASE WHEN C.CASE_STATUS = 10 THEN

    CASE WHEN E.REJECT_REASON IS NULL THEN 'A1021006'

    WHEN E.REJECT_REASON = '71' THEN 'A1021007'

    ELSE 'A1021008'

    END

    END)

    11=A1021002

    12=A1021011

    13=A1021002

    14=A1021002

    15=A1021002

    16=A1021002

    17=A1021002

    18=A1021002

    Tried this but got an error

    ((CASE_STATUS == 10 && ISNULL( REJECT_REASON)) ? "A1021006" : ((CASE_STATUS == 10 && REJECT_REASON == "71") ? "A1021007" : "A1021008")) : (CASE_STATUS == 1 || CASE_STATUS == 2 || CASE_STATUS == 3 || CASE_STATUS == 4 || CASE_STATUS == 5 || CASE_STATUS == 6 || CASE_STATUS == 11 || CASE_STATUS == 13 || CASE_STATUS == 14 || CASE_STATUS == 15 || CASE_STATUS == 16 || CASE_STATUS == 17 || CASE_STATUS == 18) ? "A1021002" : (CASE_STATUS == 7 || CASE_STATUS == 8) ? "A1021003" : CASE_STATUS == 12 ? "A1021011" : "")

    Thanks in advance!!

  • You probably need to stage the data in a table, then use T-SQL as Source Component and write CASE statement in T-SQL.

    Thomas

    Thomas LeBlanc, MVP Data Platform Consultant

  • It would be useful to know the exact error condition being generated. However I would work towards simplifying the expression by

    a) including an additional OLE SRCE dataset which included the basic transformations, ie

    SELECT 1 AS CASE_STATUS

    ,'A1021002' AS RESULT

    UNION ALL

    SELECT 2 AS CASE_STATUS

    ,'A1021002' AS RESULT

    etc etc

    UNION ALL

    SELECT 10 AS CASE_STATUS

    ,null AS RESULT

    etc etc

    b) doing a merge join wth the primary data source to return the results for everything other than case_status 10

    c) having the derived column expression to include the additonal logic for Case_Status 10 and the associated Reject_Reason (s)

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply