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.