SSIS RIGHT Function

The SSIS RIGHT is a string function that returns the specified number of right part or rightmost characters from the string column or character expression. This article explains how to use the RIGHT function with an example and the syntax for returning the right part of the character expression is as shown below.

RIGHT(<<Character_expression>>, <<numbers>>)

Character_expression: Original String or the Column name.

Numbers: Total number of characters the RIGHT function has to return from the right side.

To demonstrate the SSIS RIGHT function, we use the Employee table below, which has 15 records. Please refer to the Union All Transformation, Derived Column Transformation, Built-in Functions, and SQL Server articles in SSIS.

Source Table

SSIS RIGHT function

For this, let me add a Data Flow Task and double-click on it to enter the Data Flow region. Next, add the OLE DB Source to read data from the above SQL database table. Please join the OLE DB Source to Derived Column Transformation because it helps to write RIGHT function expressions and create new columns.

Data Flow to add Derived Column

Next, double-click on it to open the Derived Column Transformation Editor. Drag and drop the SSIS RIGHT function to the row cell under the Expression and replace the Character_expression with the column name and numbers with an integer (10). Either you can manually delete the complete expression or drag and drop the column will automatically replace it.

The below expression returns the rightmost (right part) ten characters from the name field.

RIGHT(Name,10)
SSIS RIGHT Function Expression

The below expression uses the FINDSTRING function to return the location of the @ symbol. The LEN function to get the Name length. Next, the SSIS RIGHT function returns the right part of the email address (Domain Name) from the @ symbol.

RIGHT([Email Adress], LEN([Email Adress]) - FINDSTRING([Email Adress],"@",1))

Click OK to close the Editor window.

Drag and drop the Union All Transformation and connect the Derived Column to it. Next, right-click on the connection node and choose the Enable Data Viewer. Please run the SSIS RIGHT function package to see the right part of the name and domain name from the email address column.

SSIS RIGHT Function package output