New SSIS 2012 Designer Features

  • 9/15/2012
Contents

Expressions

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

NOTE

For examples on how to use the new SSIS expression functions, please see the Functions entry in Books Online: http://msdn.microsoft.com/en-us/library/ms141671(v=SQL.110).aspx.

LEFT Function

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.

REPLACENULL Function

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.

NOTE

The REPLACENULL function works with most data types (everything except the LOB types—DT_IMAGE, DT_TEXT, and DT_NTEXT), but both expressions should evaluate to the same data type. If there is a data type mismatch between the arguments, the function will attempt to cast the value of the second expression to the data type of the first. If the cast cannot be done, the expression will return an error.

TOKEN Function

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.

NOTE

The underlying implementation of the TOKEN and TOKENCOUNT functions is based on the strtok C++ function. For more information on how the function determines how to split the string into tokens, see the entry in Books Online: http://msdn.microsoft.com/en-us/library/2c8d19sb.aspx.

TOKENCOUNT Function

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.

Save to your account

This chapter is from the book