SSIS 2012 has added a few new expression functions and I thought I’d spend a short time detailing each.
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.
LEFT( «character_expression», «number» )
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.
TOKEN( «character_expression», «delimiter_expression», «occurrence» )
TOKEN("new expressions can be fun"," ",2)
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.
TOKENCOUNT( «character_expression», «delimiter_expression» )
TOKENCOUNT("new expressions can be fun"," ")
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.
REPLACENULL( «expression», «expression» )
REPLACENULL( [OrderDateSK] , 19000101 )
19000101 (if OrderDateSK is evaluated as NULL)
Somehow I missed the “Token” expression in all of the What’s New things I read/watched. Thanks for pointing that out. All of these are nice additions to the expression language.
Same here Peter. When I saw there were other new ones I searched through to see if there were any others and found that one.
I enjoyed reading this post and recommend others to read.
You seem to be an expert in the tech and how about writing some more functions and expressions examples.
Please guide me to become an expert.
As and when you post please inform me so that I could learn some more.
Thanks for educating the community and appreciate your volunteer-ship.
Anctiously waiting for some more functions.
Thanks a bunch