Comparison between T-SQL and SSIS expression

An Expression is a combination of identifiers, literals, functions, and operators that returns a single data value. We are very familiar with functions and operators in T-SQL. But writing an expression in SSIS is not exactly same as in T-SQL. But that is not so much different also to worry about. If we know the difference between the functions and operators of T-SQL and SSIS then we can save our time of learning the SSIS expression separately.

 

Operators: Besides the following 5, all other operators are same in T-SQL and SSIS.

 

	T-SQL	SSIS
Logical AND	AND	&&
Logical OR	OR	||
Logical Not	NOT	!
Equal	=	==
Conditional	IF boolean_expression           expression1 ELSE  expression2	boolean_expression ? expression1 : expression2

 

Functions: In SSIS, functions of following 4 categories are supported.

·         Mathematical functions: To perform calculations based on numeric input values provided as parameters to the functions and return numeric values. Following mathematical functions only are supported in SSIS. Functionality of these functions is same in SSIS and T-SQL:

ABS	LN	SIGN
EXP	LOG	SQUARE
CEILING	POWER	SQRT
FLOOR	ROUND

 

·         String functions:  To perform operations on string or hexadecimal input values and return a string or numeric value.

	T-SQL	SSIS
Search a string in another	CHARINDEX	FINDSTRING
Remove leading trailing spaces	LTRIM and RTRIM	TRIM

 

Following other string functions are supported in SSIS. Functionality of these functions is same as T-SQL:

HEX	REPLACE	RTRIM
LEN	REPLICATE	SUBSTRING
LOWER	REVERSE	UPPER
LTRIM	RIGHT

 

·         Date and time functions: To perform operations on date and time values and return string, numeric, or date and time values. Only one T-SQL date function DATENAME is not supported by SSIS. All other functions are same in T-SQL and SSIS.

·         System functions.

 

Few other differences are as following:

 

	T-SQL	SSIS
Data type conversion	CAST, CONVERT	(DATA_TYPE) Expression
Behavior of NULL	Determines whether or not a given expression is NULL.	Returns a null value of a requested data type.