Functions (SSIS Expressions)

The expression language includes a set of functions for use in expressions. An expression can use a single function, but typically an expression combines functions with operators and uses multiple functions.

The functions can be categorized into the following groups:

  • Mathematical functions that perform calculations based on numeric input values provided as parameters to the functions and return numeric values.

  • String functions that perform operations on string or hexadecimal input values and return a string or numeric value.

  • Date and time functions that perform operations on date and time values and return string, numeric, or date and time values.

  • System functions that return information about an expression.

The expression language provides the following mathematical functions.

Function

Description

ABS (SSIS Expressions)

Returns the absolute, positive value of a numeric expression.

EXP (SSIS Expressions)

Returns the exponent to base e of the specified expression.

CEILING (SSIS Expressions)

Returns the smallest integer that is greater than or equal to a numeric expression.

FLOOR (SSIS Expressions)

Returns the largest integer that is less than or equal to a numeric expression.

LN (SSIS Expressions)

Returns the natural logarithm of a numeric expression.

LOG (SSIS Expressions)

Returns the base-10 logarithm of a numeric expression.

POWER (SSIS Expressions)

Returns the result of raising a numeric expression to a power.

ROUND (SSIS Expressions)

Returns a numeric expression that is rounded to the specified length or precision. .

SIGN (SSIS Expressions)

Returns the positive (+), negative (-), or zero (0) sign of a numeric expression.

SQUARE (SSIS Expressions)

Returns the square of a numeric expression.

SQRT (SSIS Expressions)

Returns the square root of a numeric expression.

The expression evaluator provides the following string functions.

Function

Description

CODEPOINT (SSIS Expressions)

Returns the Unicode code value of the leftmost character of a character expression.

FINDSTRING (SSIS Expressions)

Returns the one-based index of the specified occurrence of a character string within an expression.

HEX (SSIS Expressions)

Returns a string representing the hexadecimal value of an integer.

LEN (SSIS Expressions)

Returns the number of characters in a character expression.

LOWER (SSIS Expressions)

Returns a character expression after converting uppercase characters to lowercase characters.

LTRIM (SSIS Expressions)

Returns a character expression after removing leading spaces.

REPLACE (SSIS Expressions)

Returns a character expression after replacing a string within the expression with either a different string or an empty string.

REPLICATE (SSIS Expressions)

Returns a character expression, replicated a specified number of times.

REVERSE (SSIS Expressions)

Returns a character expression in reverse order.

RIGHT (SSIS Expressions)

Returns part of a character string starting at a specified number of characters from the right.

RTRIM (SSIS Expressions)

Returns a character expression after removing trailing spaces.

SUBSTRING (SSIS Expressions)

Returns a part of a character expression.

TRIM (SSIS Expressions)

Returns a character expression after removing leading and trailing spaces.

UPPER (SSIS Expressions)

Returns a character expression after converting lowercase characters to uppercase characters.

The expression evaluator provides the following date and time functions.

Function

Description

DATEADD (SSIS Expressions)

Returns a new DT_DBTIMESTAMP value by adding a date or time interval to a specified date.

DATEDIFF (SSIS Expressions)

Returns the number of date and time boundaries crossed between two specified dates.

DATEPART (SSIS Expressions)

Returns an integer representing a datepart of a date.

DAY (SSIS Expressions)

Returns an integer that represents the day of the specified date.

GETDATE (SSIS Expressions)

Returns the current date of the system.

GETUTCDATE (SSIS Expressions)

Returns the current date of the system in UTC time (Universal Time Coordinate or Greenwich Mean Time).

MONTH (SSIS Expressions)

Returns an integer that represents the month of the specified date.

YEAR (SSIS Expressions)

Returns an integer that represents the year of the specified date.

The expression evaluator provides the following null functions.

Function

Description

ISNULL (SSIS Expressions)

Returns a Boolean result based on whether an expression is null.

NULL (SSIS Expressions)

Returns a null value of a requested data type.

Expression names are shown in uppercase characters, but expression names are not case-sensitive. For example, using "null" works as well as using "NULL".