New SSIS 2012 Expressions

SSIS 2012 has added a few new expression functions and I thought I’d spend a short time detailing each.

LEFT

The LEFT function believe it or not is a new function.  In past versions of SSIS you had a RIGHT function available but no LEFT.  Well SSIS expression haters you now have what you want.  This function is described as:

Returns the left part of a character expression with the specified number of characters.

Template:

LEFT( «character_expression», «number» )

Example:

LEFT( [ProductName],3)

Result:

Nik

TOKEN

The new TOKEN expression is an interesting one.  It returns a string after a specified Token delimiter.  You can pass in multiple delimiters for you expression to parse and also specify the occurrence number you would like to return.  That means if you set the occurrence to 3 it would return the third instance of the token.  SSIS describes this function as:

Returns the specified occurrence of a token in a string. A token may be marked by a delimiter in a specified set of delimiters. The function returns an empty string if the occurrence is not found. The string parameter must evaluate to a character expression, and the occurrence parameter must evaluate to an integer.

Template:

TOKEN( «character_expression», «delimiter_expression», «occurrence» )

Example:

TOKEN("new expressions can be fun"," ",2)

Result:

expressions

TOKENCOUNT

TOKENCOUNT would likely be used in combination with the previously discussed TOKEN function.  The TOKENCOUNT function returns back the number of times a Token delimiter appears in a string value.  This would likely be plugged into the TOKEN expressions for the number of occurrences when trying to find the last occurrence.  SSIS describes this function as:

Returns the number of tokens in a string. A token may be marked by a delimiter in a specified set of delimiters. The string parameter must evaluate to a character expression.

Template:

TOKENCOUNT( «character_expression», «delimiter_expression» )

Example:

TOKENCOUNT("new expressions can be fun"," ")

Result:

5

REPLACENULL

Again this function answers the SSIS haters who don’t like the fact that there is an ISNULL function in the expression language but it doesn’t work like the T-SQL ISNULL. Currently if you wanted to accomplish the T-SQL ISNULL you would have do write an expression like this:

ISNULL(OrderDateSK) ? 19000101 : OrderDateSK

This uses the ISNULL function that returns back True or False if the field is NULL and also uses a conditional operator to determine how to react when it is NULL.  The REPLACENULL function will work much more like the T-SQL ISNULL function.  This function is described as:

Returns the value of the second expression parameter if the first expression parameter is null.

Template:

REPLACENULL( «expression», «expression» )

Example:

REPLACENULL(  [OrderDateSK] , 19000101 )

Result:

19000101 (if OrderDateSK is evaluated as NULL)