SSIS equivalent of a case when

Question

SSIS equivalent of a case when

david_h_edmonds

Default port

Points: 1430
More actions
February 20, 2018 at 7:51 am

#333691

Hi guys new poster here so treat me gently please 🙂
I am writing an ETL routing with SSIS and I have got stuck with some conditional splits and how to handle them.
There is a column called "S" in the raw data that has various stati that the call can be at.
Examples:

Answered
Tr/Pk/Rc/Pu
Diverted
TrTo
Failed
Advanced
[UnMon Ent]
P/U From Grp
ABANDONED
I then lookup S in a translation table as follows
S Derived_Status

Answered PRESENTED
Tr/Pk/Rc/Pu PRESENTED
Diverted DIVERTED
TrTo TRANSFERRED
Failed FAILED
Advanced PRESENTED
[UnMon Ent] FAILED
P/U From Grp PRESENTED
ABANDONED ABANDONED

The issue I have is that I need to calculate an additional status based on the value in anther field.
The output will be 2 derived columns
called
FINAL_STATUS_45 and FINAL_STATUS_60
The 45 and 60 relate to the length of time the call rang for before being answered.
So
if a call rings for less than 45 seconds and has a derived status of ABANDONED, I want FINAL_STATUS_45 to take the value "QUICK ABANDONED" ELSE take the value of DERIVED_STATUS
if a call rings for less than 60 seconds and has a derived status of ABANDONED, I want FINAL_STATUS_60 to take the value "QUICK ABANDONED" ELSE take the value of DERIVED_STATUS
I am struggling to work out how to add in the "QUICK ABANDONED" as the result of the conditional split.
My conditional split expressions are so far:
Can anyone help me on how to achieve this please?
Regards
Dave

Viewing 7 posts - 1 through 6 (of 6 total)

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

david_h_edmonds Default port Points: 1430 More actions · Answer 1

Hi, I have come up with a different solution, not quite as sustainable with high data voulmes but as the volume will be relatively low for this process, I have the data source as an SQL statement and used case whens so that the fields exist when the data is imported into the data flow.
What are the pitfalls of this?

Cheers

Dave

cwe424 SSC Veteran Points: 256 More actions · Answer 2

Hey David,

It sounds like you may have already started to explore an alternate solution, but I didn't see you mention the ternary operator in SSIS, which operates like : [Condition Statement] ? [True outcome] : [False outcome]. You can chain them together to put another condition in the [False outcome] section to get multiple branches/cases to get what you need. In my understanding, a Derived column that results in a value being placed into a column might be more straight forward than doing a conditional split and sending the data in different directions. That last part is possibly not applicable, just trying to draw some conclusions on what you have presented. Meanwhile, if you were already aware of and are using the ternary operator, my suggestion probably doesn't help much, but, personally, when I think of a Case statement, I find myself using the ternary operator in a derived column rather than using the conditional split, unless I truly need to send data to different destinations and data flow transformations. I hope that helps.

david_h_edmonds Default port Points: 1430 More actions · Answer 3

Hi cwe242 thanks for the suggestion. I shall try it tonight and let you know how it works. Regards

Dave

Phil Parkin SSC Guru Points: 246723 More actions · Answer 4

david_h_edmonds - Tuesday, February 20, 2018 9:57 AM
Hi, I have come up with a different solution, not quite as sustainable with high data voulmes but as the volume will be relatively low for this process, I have the data source as an SQL statement and used case whens so that the fields exist when the data is imported into the data flow.
What are the pitfalls of this?
Cheers
Dave

This is a decent solution ... there are no particular pitfalls.
I would, however, suggest that you consider creating 'DerivedStatus' as a permanent physical mapping table and using that in your query, rather than CASE.
If you decide that you really want to code all of this in SSIS instead, it would be simple enough to write a few lines of code in a Script Component to implement the logic for you. Much easier to read and understand (IMO) than a derived column containing multiple nested (condition?True:False) expressions.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

david_h_edmonds Default port Points: 1430 More actions · Answer 5

Hi Phil, thanks for the reply. Derived_Status is already a mapping table field that I am joining to in the SQL, the case when is simply:

Case when Derived status = ‘abandoned’ and ring_ secs <= 45 then ‘Quick Abandoned’ else Derived_Status End

and the same for the 60 second field

Phil Parkin SSC Guru Points: 246723 More actions · Answer 6

david_h_edmonds - Tuesday, February 20, 2018 11:51 AM
Hi Phil, thanks for the reply. Derived_Status is already a mapping table field that I am joining to in the SQL, the case when is simply:Case when Derived status = â€˜abandonedâ€™ and ring_ secs <= 45 then â€˜Quick Abandonedâ€™ else Derived_Status End and the same for the 60 second field

Cool: nice and simple.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Answered	PRESENTED
Tr/Pk/Rc/Pu	PRESENTED
Diverted	DIVERTED
TrTo	TRANSFERRED
Failed	FAILED
Advanced	PRESENTED
[UnMon Ent]	FAILED
P/U From Grp	PRESENTED
ABANDONED	ABANDONED