Implicit Data Type Conversion in Expressions
An implicit conversion of a data type occurs when the expression evaluator automatically converts the data from one data type to another. For example, if a smallint is compared to an int, the smallint is implicitly converted to int before the comparison is performed.
The expression evaluator cannot perform implicit data conversion when the arguments and operands have incompatible data types. In addition, the expression evaluator cannot implicitly convert any value to a Boolean. Instead, the arguments and operands must be explicitly converted by using the cast operator. For more information, see Cast (SSIS Expression): Convert SSIS Data Types.
The following diagram shows the result type of implicit conversions of BINARY operations. The intersection of column and row in this table is the result type of a binary operation with operands of the left (From) and right (To) types.
The intersection of a signed and an unsigned integer is a signed integer that is potentially larger than either argument.
Operators compare strings, dates, Booleans, and other data types. Before an operator compares two values, the expression evaluator performs certain implicit conversions. For more information, see the following topics:
A function that uses a single argument returns a result with the same data type as the argument, with the following exceptions:
DAY, MONTH, and YEAR accept a date and return an integer (DT_I4) result.
ISNULL accepts an expression of any SSIS data type and returns a Boolean (DT_BOOL) result.
SQUARE and SQRT accept a numeric expression and return a non-integral numeric (DT_R8) result.
If the arguments have the same data type, the result is of that type. The only exception is the result of a binary operation on two values with the DT_DECIMAL data type, which returns a result with the DT_NUMERIC data type.
Technical article, SSIS Expression Cheat Sheet, on pragmaticworks.com