SSIS - How to parse extensions from path-filename strings
Firstly, the IF/IIF you are looking for is the "?" Conditional Operator

? Conditional

One way to implement the Derived Column would be to parse the string like this

 TOKENCOUNT( TOKEN( REVERSE( RTRIM( name ) ), "\\", 1), "." ) == 1
 ? "" : REVERSE( TOKEN( TOKEN( REVERSE( RTRIM( name ) ), "\\", 1), ".", 1) )


In this case, "name" is the column that holds the string to be parsed.

What we are doing here is:


Remove any trailing white-space with RTRIM( name )
Reverse the string so we are dealing with the end as the beginning with REVERSE()
Grab the first "token" delimited by a backslash using TOKEN()
Count how many "." delimiters are in the result using TOKENCOUNT()
Test the count of "." delimited tokens is equal to 1 using ?
If the count is 1, return an empty string - no extension.
Otherwise return the first token before the "." using TOKEN()
Reverse the extension to get back to "normal" using REVERSE()