Removal of the Character Limit
SQL Server 2012 has removed the 4,000-character limit in SSIS expressions that existed in previous versions of the product. This limit affected users in two ways; the physical length of the expression string had to be fewer than 4,000 characters, and strings created by the expression would be truncated if they went over the limit. It was possible to set a string greater than 4,000 characters in length to a variable using a Script task, but this workaround is no longer needed. The character limit is gone from all of the places that use the SSIS expression engine, including property expressions, the Derived Column transform, and the Conditional Split transform.
New Expression Functions
The LEFT function returns the specified number of characters from the beginning of a string.
LEFT ( <string>, <number> )
This is similar to the RIGHT function, which returns characters from the end of the string. In previous versions of SSIS, you’d accomplish this with the SUBSTRING function, starting at the first character.
The REPLACENULL function returns the value of the second expression argument if the first expression argument evaluates to NULL.
REPLACENULL ( <expression_1>, <expression_2> )
If the first expression argument is not NULL, then it is returned instead. This function is useful for substituting a NULL value with a default value in a Derived Column transform. REPLACENULL provides an alternate, easier to use syntax than performing a NULL check with the Conditional (? :) operator.
The TOKEN function splits the given string (first argument) into a set of tokens using the delimiters (second argument), and returns the specified occurrence (third argument).
TOKEN ( <string>, <delimiters>, <occurrence> )
Note that the occurrence index starts at 1, and not 0. To get the first token occurrence in the string, you’d use a value of 1. If the specified occurrence is higher than the number of actual tokens in the string, the function returns an empty string. You can use the TOKENCOUNT function to determine how many tokens exist in the string. The delimiter argument is a string containing one or more characters. Each character in the string is treated as a separate delimiter.
The TOKENCOUNT function splits a string the same way that the TOKEN function does.
TOKENCOUNT ( <string>, <delimiters> )
Instead of returning individual tokens from the string, it returns the total number of tokens within that string. It can be used to easily determine the value to use to retrieve the last token in a string. For example, when splitting a file path, TOKENCOUNT can be used to determine the occurrence value to use to return the file name.