What is wrong with this syntax

Question

Hi All,
I have a derived column expression as below
((CaseTypeIDFound == 1 || CaseTypeIDFound == 5|| CaseTypeIDFound == 6|| CaseTypeIDFound == 7|| CaseTypeIDFound == 8|| CaseTypeIDFound == 9|| CaseTypeIDFound == 10|| CaseTypeIDFound == 11|| CaseTypeIDFound == 12|| CaseTypeIDFound == 13|| CaseTypeIDFound == 14|| CaseTypeIDFound == 17|| CaseTypeIDFound == 23|| CaseTypeIDFound == 24|| CaseTypeIDFound == 25|| CaseTypeIDFound == 26|| CaseTypeIDFound == 27|| CaseTypeIDFound == 28|| CaseTypeIDFound == 29|| CaseTypeIDFound == 30|| CaseTypeIDFound == 31|| CaseTypeIDFound == 32|| CaseTypeIDFound == 42|| CaseTypeIDFound == 43|| CaseTypeIDFound == 47|| CaseTypeIDFound == 50|| CaseTypeIDFound == 58) ? "1" :
(CaseTypeIDFound == 76 || CaseTypeIDFound == 78 || CaseTypeIDFound == 80 || CaseTypeIDFound == 82 || CaseTypeIDFound == 84 || CaseTypeIDFound == 86|| CaseTypeIDFound == 88 || CaseTypeIDFound == 90 || CaseTypeIDFound == 92 || CaseTypeIDFound == 94) ? "3" : "2"))

I tried different parenthesis but still getting the below error, can someone please advice me. Is there a limit on how many values can be passed OR is there a way i can use IN(VALUES)
Error Message
TITLE: Microsoft Visual Studio

---

Error at Load New Filing Cases [Derived Column [218]]: Attempt to parse the expression "((CaseTypeIDFound == 1 || CaseTypeIDFound == 5|| CaseTypeIDFound == 6|| CaseTypeIDFound == 7|| CaseTypeIDFound == 8|| CaseTypeIDFound == 9|| CaseTypeIDFound == 10|| CaseTypeIDFound == 11|| CaseTypeIDFound == 12|| CaseTypeIDFound == 13|| CaseTypeIDFound == 14|| CaseTypeIDFound == 17|| CaseTypeIDFound == 23|| CaseTypeIDFound == 24|| CaseTypeIDFound == 25|| CaseTypeIDFound == 26|| CaseTypeIDFound == 27|| CaseTypeIDFound == 28|| CaseTypeIDFound == 29|| CaseTypeIDFound == 30|| CaseTypeIDFound == 31|| CaseTypeIDFound == 32|| CaseTypeIDFound == 42|| CaseTypeIDFound == 43|| CaseTypeIDFound == 47|| CaseTypeIDFound == 50|| CaseTypeIDFound == 58) ? "1" : " failed. The expression might contain an invalid token, an incomplete token, or an invalid element. It might not be well-formed, or might be missing part of a required element such as a parenthesis.

Error at Load New Filing Cases [Derived Column [218]]: Cannot parse the expression "((CaseTypeIDFound == 1 || CaseTypeIDFound == 5|| CaseTypeIDFound == 6|| CaseTypeIDFound == 7|| CaseTypeIDFound == 8|| CaseTypeIDFound == 9|| CaseTypeIDFound == 10|| CaseTypeIDFound == 11|| CaseTypeIDFound == 12|| CaseTypeIDFound == 13|| CaseTypeIDFound == 14|| CaseTypeIDFound == 17|| CaseTypeIDFound == 23|| CaseTypeIDFound == 24|| CaseTypeIDFound == 25|| CaseTypeIDFound == 26|| CaseTypeIDFound == 27|| CaseTypeIDFound == 28|| CaseTypeIDFound == 29|| CaseTypeIDFound == 30|| CaseTypeIDFound == 31|| CaseTypeIDFound == 32|| CaseTypeIDFound == 42|| CaseTypeIDFound == 43|| CaseTypeIDFound == 47|| CaseTypeIDFound == 50|| CaseTypeIDFound == 58) ? "1" : ". The expression was not valid, or there is an out-of-memory error.

Error at Load New Filing Cases [Derived Column [218]]: The expression "((CaseTypeIDFound == 1 || CaseTypeIDFound == 5|| CaseTypeIDFound == 6|| CaseTypeIDFound == 7|| CaseTypeIDFound == 8|| CaseTypeIDFound == 9|| CaseTypeIDFound == 10|| CaseTypeIDFound == 11|| CaseTypeIDFound == 12|| CaseTypeIDFound == 13|| CaseTypeIDFound == 14|| CaseTypeIDFound == 17|| CaseTypeIDFound == 23|| CaseTypeIDFound == 24|| CaseTypeIDFound == 25|| CaseTypeIDFound == 26|| CaseTypeIDFound == 27|| CaseTypeIDFound == 28|| CaseTypeIDFound == 29|| CaseTypeIDFound == 30|| CaseTypeIDFound == 31|| CaseTypeIDFound == 32|| CaseTypeIDFound == 42|| CaseTypeIDFound == 43|| CaseTypeIDFound == 47|| CaseTypeIDFound == 50|| CaseTypeIDFound == 58) ? "1" : " on "Derived Column.Outputs[Derived Column Output].Columns[DivisionID]" is not valid.

Error at Load New Filing Cases [Derived Column [218]]: Failed to set property "Expression" on "Derived Column.Outputs[Derived Column Output].Columns[DivisionID]".

---

ADDITIONAL INFORMATION:

Exception from HRESULT: 0xC0204006 (Microsoft.SqlServer.DTSPipelineWrap)

---

BUTTONS:

OK

---

Thanks

Answer 1

Hi @bk ,

I would suggest a different technique to achieve the same result.
It is based on use of the SSIS FINDSTRING() function.
It simulates SQL's IN (...) clause.
It makes the expression much more compact and manageable.

Check it out below.
Somehow this forum strips off undescore chars. That's why I am adding a screen shot of it too.

(FINDSTRING("_1_5_6_7_8_9_10_11_12_13_14_17_23_24_25_26_27_28_29_30_31_32_42_43_47_50_58_", "_" + (DT_WSTR,10)CaseTypeIDFound + "_", 1) > 0) ? "1" :
(FINDSTRING("_76_78_80_82_84_86_88_90_92_", "_" + (DT_WSTR,10)CaseTypeIDFound + "_", 1) > 0) ? "3" : "2"

Answer 2

Thanks @Yitzhak Khabinsky
No luck please see the below error