Case Statement in SSIS epression

Question

I have a case statement where I am checking for multiple string in a column how can I achieve it as derived column expression. For e.g. I am checking for 'Today' or 'Sunday' in a string 'Today is Sunday'. After a little research I found that I can use Findstring function, but it checks for one condition at a time. Is there a way I can use like a nested Findstring.

I can use SQL task and achieve the expected results but was wondering if there's a better way of doing it.

Thanks,
Ali.

Answer 1

Hi @Ali Ahad ,

Remove the (DT_STR,255,1252) in your expression first and then you can change the data types from the Advanced editor by right click on Derived Column Transformation -> Show Advanced Editor -> Input and output column properties. Here you can change the data types to DT_STR.

The other option is you can use data conversion transformation after the Derived Column Transformation.

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 2

Hi Zoe,

Yes I did use the Data Conversion transformation to achieve the expected results.

Thanks,
Ali.

Answer 3

Hi @Ali Ahad ,

Do you mean that you want to do Nested If in SSIS derived column expression?

If yes, you may try with below expression.

Field == "TrueCondition1" ? “True” : ( Field == "TrueCondition2" ? “True” : ( Field == "TrueCondition3 " ? “True” : ( Field == "TrueCondition4" ? “True” : “False"  ) ) )  

A same thread you may take a reference to witing-a-case-statement-in-ssis.

If I misunderstand your issue, you may provide some sample data and your expected result for us to do more test.

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 4

Hi Zoe,

I am trying to write a case statement using Findstring as I have like condition in there and have to check for multiple conditions. My sql statement is like:

Case when column like %value% or like %value% then do something.
End

Hope this helps.

Thanks,
Ali.

Answer 5

So I was able to figure it out the expression but my data type switched to Unicode String [DT_WSTR] and I want to switch it to (DT_STR,255,1252). I tried to put the (DT_STR,255,1252) before the FINDSTRING but get conversion error.

Here is my code:
(FINDSTRING((DT_STR,255,1252)LTRIM(RTRIM([Device Host Name])), "value", 1) >0 ||
FINDSTRING((DT_STR,255,1252)LTRIM(RTRIM([Device Host Name])), "value", 1) >0 ||
FINDSTRING((DT_STR,255,1252)LTRIM(RTRIM([Device Host Name])),"value", 1) >0 ) ? "value" : (
(FINDSTRING((DT_STR,255,1252)LTRIM(RTRIM([Device Host Name])),"value", 1) >0 ||
FINDSTRING((DT_STR,255,1252)LTRIM(RTRIM([Device Host Name])),"value", 1) >0 ) ? "value" : (
FINDSTRING((DT_STR,255,1252)LTRIM(RTRIM([Device Host Name])),"value", 1)>0 ? "value" : (
FINDSTRING((DT_STR,255,1252)LTRIM(RTRIM([Device Host Name])), "value", 1)>0 ? "value" : "value" )))

Thanks,
Ali.