Integration Services Data Types in Expressions
The expression evaluator uses Integration Services data types. When data first enters a data flow in an Integration Services package, the data flow engine converts all column data to an Integration Services data type, and the column data that an expression uses already has an Integration Services data type. Expressions used in the Conditional Split and the Derived Column transformations can reference columns because they are part of a data flow that includes column data.
Expressions can also use variables. Variables have a Variant data type and the expression evaluator converts the data type of a variable from a Variant subtype to an Integration Services data type before it evaluates the expression. Variables can use only a subset of the Integration Services data types. For example, a variable cannot use a Binary Large Object Block (BLOB) data type.
For more information about Integration Services data types and the mapping of Variant data types to Integration Services data types, see Integration Services Data Types.
In addition, expressions can include string, Boolean, and numeric literals. The expression evaluator always converts string literals to the DT_WSTR data type and converts Boolean literals to the DT_BOOL data type. The expression evaluator interprets all values enclosed in quotation marks as strings. Numeric literals are converted to one of the numeric Integration Services data types. For more information about converting numeric literals to numeric Integration Services data types, see Literals (SSIS Expressions).
Note
Boolean values are logical values, not numbers. Although Boolean values may be displayed as numbers in some environments, they are not stored as numbers, and various programming languages represent Boolean values as numeric values differently, as do the .NET Framework methods.
For example, the conversion functions available in Visual Basic convert True to -1; however, the System.Convert.ToInt32 method in the .NET Framework converts True to +1. The Integration Services Expression Language converts True to -1.
To avoid errors or unexpected results, you should not write code that relies on particular numeric values for True and False. Wherever possible, you should restrict usage of Boolean variables to the logical values for which they are designed.
Requirements for Data Used in Expressions
The expression evaluator supports all Integration Services data types. However, depending on the operation or the function, the operands and arguments require certain data types. The expression evaluator imposes the following data type requirements on data used in expressions:
Operands used in logical operations must evaluate to a Boolean. For example, ColumnA > 1&&ColumnB < 2.
Operands used in mathematical operations must evaluate to a numeric value. For example, 23.75 * 4.
Operands used in comparison operations, such as logical and equality operations, must evaluate to compatible data types.
For example, one of the expressions in the following example uses the DT_DBTIMESTAMPOFFSET data type:
(DT_DBTIMESTAMPOFFSET,3) "1999-10-11 20:34:52.123 -3:30" != (DT_DBDATE)"1999-10-12"
The system converts the expression, (DT_DBDATE)"1999-10-12", to DT_DBTIMESTAMPOFFSET. The example evaluates to TRUE because the converted expression becomes "1999-10-12 00:00:00.000 +00:00", which is not equal to the value of the other expression, (DT_DBTIMESTAMPOFFSET,3) "1999-10-11 20:34:52.123 -3:30".
Arguments passed to mathematical functions must evaluate to a numeric data type. Depending on the function or operation, a specific numeric data type may be required. For example, the HEX function requires a signed or unsigned integer.
Arguments passed to string functions must evaluate to a character data type: DT_STR or DT_WSTR. For example, UPPER("flower"). Some string functions, such as SUBSTRING, require additional integer arguments for the start position and the length of the string.
Arguments passed to date and time functions must evaluate to a valid date. For example, DAY(GETDATE()). Some functions, such as DATEADD, require an additional integer argument for the number of days the function adds to a date.
Operations that combine an unsigned eight-byte integer and a signed integer require an explicit cast to clarify the result format. For more information, see Cast (SSIS Expressions).
Results of many operations and functions have predetermined data types. This can be the data type of the argument or the data type to which the expression evaluator casts the result. For example, the result of a logical OR operator (||) is always a Boolean, the result of the ABS function is the numeric data type of the argument, and the result of multiplication is the smallest numeric data type that can hold the result without loss. For more information about the data types of results, see Operators (SSIS Expressions) and Functions (SSIS Expressions).