SSIS Conditional Expressions (Lightbulb:On)

I’ve often been confused and frustrated by conditionals ( … ? … : … ) in SSIS expressions. The concept is straightforward enough, but the syntax made it really hard for me to keep track in nontrivial cases. Then yesterday I had an epiphany: it’s much easier to keep them straight if you write them on multiple lines. So this…

A == 1 ? “One” :
A == 2 ? “Two” :
“Neither one nor two”

…is equivalent to something like this in other languages…

if (A == 1) {
“One”
} else if (A == 2) {
“Two”
} else {
“Neither one nor two”
}

OK, so that was a trivial example, but what I was working on required different logic for different days of the week, along with type casts and other clutter:

(DT_DATE) (DT_DBDATE) (
DATEPART( “weekday”, GETDATE() ) == 2 ? (DT_DATE) DATEADD( “day”, 2, GETDATE() ) :
DATEPART( “weekday”, GETDATE() ) == 3 ? (DT_DATE) DATEADD( “day”, 2, GETDATE() ) :
DATEPART( “weekday”, GETDATE() ) == 4 ? (DT_DATE) DATEADD( “day”, 2, GETDATE() ) :
DATEPART( “weekday”, GETDATE() ) == 5 ? (DT_DATE) DATEADD( “day”, 4, GETDATE() ) :
DATEPART( “weekday”, GETDATE() ) == 6 ? (DT_DATE) DATEADD( “day”, 4, GETDATE() ) :
(DT_DATE) “12/31/9999”
)

That would have been a nightmare to write and maintain, if not for this trick.

Obviously, SSIS doesn’t give you a multi-line editor everywhere you can use expressions, but you can just write them initially in Sublime Text and copy-and-paste them into SSIS.