SSIS Functions

SQL Server Integration Services (SSIS) provides a few built-in Mathematical, String, Date and time, and Null functions to transform the source data before exporting it to the destination tables. This article lists the available functions and describes them.

To understand each function in detail, please click the hyperlink, which will explain the function along with a practical example. We use derived column transformation to demonstrate these SSIS functions. So, please refer to the Derived Column Transformation article in SSIS.

SSIS Mathematical Functions

The list of Mathematical functions in SSIS is shown below.

  1. ABS: It returns the absolute positive value of a given numeric expression.
  2. CEILING: It returns the nearest small integer value greater than or equal to a given numeric expression.
  3. EXP: It returns an exponential value that is e raised to the power of a numeric expression.
  4. FLOOR: It returns the nearest large integer value that is less than or equal to a given numeric expression.
  5. LN: It returns the natural logarithm value of base e for a given numeric expression.
  6. LOG: It returns a given numeric expression’s base 10 logarithm value.
  7. POWER: It returns the given numeric expression raised to the power of the specified value.
  8. ROUND: It returns the nearest integer value by rounding out the double-precision floating-point value.
  9. SIGN: It returns the sign of an 8-bit signed integer.
  10. SQUARE: It returns the square of a given numeric expression.
  11. SQRT: It returns the square root of a given numeric expression.

SSIS String Functions

The list of String functions in SSIS is shown below.

  1. CODEPOINT: It returns the Unicode code value of the leftmost character from a character expression.
  2. FINDSTRING: It searches for the given substring inside a character expression and returns the location of the specified occurrence of a string.
  3. HEX: It returns a string representing the Hexadecimal value of an integer.
  4. LEFT: It returns the specified number of leftmost (side) characters from a character expression.
  5. LEN: It returns the length or total characters in a string or character expression.
  6. LOWER: It converts the given string or character expression to Lowercase.
  7. LTRIM: It trims or removes the leading (beginning) spaces from the character expression.
  8. REPLACE: It helps to replace the matching expression with a completely new substring or an empty string.
  9. REPLICATE: It duplicates or replicates the given text/string or character expression for a given number of times.
  10. REVERSE: It performs the string Reverse (character expression in reverse order).
  11. RIGHT: It returns the specified number of Right-side characters (substring) from a character expression.
  12. RTRIM: It trims or removes the trailing (end) spaces from the character expression.
  13. SUBSTRING: It returns the part (substring) of an original string or character expression that starts at the given position, and the length determines the total characters.
  14. TOKEN: It returns the specified occurrence of a token in a character expression or string.
  15. TOKENCOUNT: It returns the total number of tokens in a character expression or string.
  16. TRIM: It removes the leading (start) and trailing (end) spaces from the character expression.
  17. UPPER: It converts the given string or character expression to Uppercase.

SSIS Date/Time Functions

The list of Date and Time functions in SSIS is shown below.

  1. DATEADD: It adds the date or time interval specified in the Datepart argument to the existing Date field to generate a feature or past DATETIME value.
  2. DATEDIFF: It returns the date or time interval difference between two date fields by comparing the Datepart.
  3. DATEPART: It returns the particular part of the Date field and returns the integer value.
  4. DAY It returns the day from the Date and returns the integer value from 1 to 31.
  5. GETDATE: It returns the current date of the local system.
  6. GETUTCDATE: It returns the system’s current date in UTC (Universal Time Coordinate).
  7. MONTH: It returns the Month from the Date and returns the integer value from 1 to 12.
  8. YEAR: It returns the Year from the Date as the integer value from 1 to 9999.

SSIS NULL Functions

The list of Nul functions to deal with NULL values in SSIS is shown below.

  1. ISNULL: It checks whether the expression is NULL or not and returns Boolean True or False.
  2. REPLACENULL: It replaces the NULLS (If any) with the specified value in the second parameter.
  3. NULL: It returns a null value of a requested Data type. It supports Integers, Unsigned Integers, Real, String (Varchar), WSTR (Nvarchar), date, boolean, decimal, currency, date, time, datetime, etc.