Syntax (SSIS Expressions)
The Integration Services expression syntax is similar to the syntax that the C and C# languages use. Expressions include elements such as identifiers (columns and variables), literals, operators, and functions. This topic summarizes the unique requirements of the expression evaluator syntax as they apply to different expression elements.
For sample expressions that use specific operators and functions, see the topic about each operator and function in the topics: Operators (SSIS Expressions) and Functions (SSIS Expressions).
For sample expressions that use multiple operators and functions as well as identifiers and literals, see Advanced Integration Services Expressions.
For sample expressions to use in property expressions, see Using Property Expressions in Packages.
In some cases, Integration Services imposes a 4000-character limit on the length of expressions. If the evaluation result of an expression has the Integration Services data type DT_WSTR or DT_STR, the expression will be truncated at 4000 characters. If the result type of a sub-expression is DT_STR or DT_WSTR, that sub-expression will likewise be truncated to 4000 characters, regardless of the overall expression result type.
Depending on the Integration Services component in which the truncation occurs, truncations are handled differently.
In transformations such as the Derived Column and Conditional Split, the truncation can be handled gracefully by configuring the transformations to ignore truncations or use an error output to redirect data rows that incurs truncation to a different output.
In the runtime, the truncation of expressions used to set values of property expressions, variables, and precedence constraints is an error and may cause the package to fail.
At design time, the truncation of a string literal generates a warning if it occurs in the pipeline, and generates an error if it occurs in the runtime.
Identifiers
Expressions can include column and variable identifiers. The columns can originate in the data source or can be created by transformations in the data flow. Expressions can use lineage identifiers to refer to columns. Lineage identifiers are numbers that uniquely identify package elements. Lineage identifiers, referenced in an expression, must include the pound (#) prefix. For example, the lineage identifier 138 is referenced using #138.
Expressions can include the system variables that SSIS provides and custom variables. Variables, when referenced in an expression, must include the @ prefix. For example, the Counter variable is referenced using @Counter. The @ character is not part of the variable name; it only indicates to the expression evaluator that the identifier is a variable. For more information, see Identifiers (SSIS Expressions).
Literals
Expressions can include numeric, string, and Boolean literals. String literals used in expressions must be enclosed in quotation marks. Numeric and Boolean literals do not take quotation marks. The expression language includes escape sequences for characters that are frequently escaped. For more information, see Literals (SSIS Expressions).
Operators
The expression evaluator provides a set of operators that provides functionality similar to the set of operators in languages such as Transact-SQL, C++, and C#. However, the expression language includes additional operators and uses different symbols than those you may be familiar with. For more information, see Operators (SSIS Expressions).
Namespace Resolution Operator
Expressions use the namespace resolution operator (::) to disambiguate variables that have the same name. By using the namespace resolution operator, you can qualify the variable with its namespace, which makes it possible to use multiple variables with the same name in a package.
Cast Operator
The cast operator converts expression results, column values, variable values, and constants from one data type to another. The cast operator provided by the expression language is similar to the one provided by the C and C# languages. In Transact-SQL, the CAST and CONVERT functions provide this functionality. The syntax of the cast operator is different from ones used by CAST and CONVERT in the following ways:
It can use an expression as an argument.
Its syntax does not include the CAST keyword.
Its syntax does not include the AS keyword.
Conditional Operator
The conditional operator returns one of two expressions, based on the evaluation of a Boolean expression. The conditional operator provided by the expression language is similar to the one provided by the C and C# languages. In multidimensional expressions (MDX), the IIF function provides similar functionality.
Logical Operators
The expression language supports the ! character for the logical NOT operator. In Transact-SQL, the ! operator is built into the set of relational operators. For example, Transact-SQL provides the > and the !> operators. The SSIS expression language does not support the combination of the ! operator and other operators. For example, it is not valid to combine ! and > into !>. However, the expression language does support a built-in != combination of characters for the not-equal-to comparison.
Equality Operators
The expression evaluator grammar provides the == equality operator. This operator is the equivalent of the = operator in Transact-SQL and the == operator in C#.
Functions
The expression language includes date and time functions, mathematical functions, and string functions that are similar to Transact-SQL functions and C# methods.
A few functions have the same names as Transact-SQL functions, but have subtly different functionality in the expression evaluator.
In Transact-SQL, the ISNULL function replaces null values with a specified value, whereas the expression evaluator ISNULL function returns a Boolean based on whether an expression is null.
In Transact-SQL, the ROUND function includes an option to truncate the result set, whereas the expression evaluator ROUND function does not.
For more information, see Functions (SSIS Expressions).