SSIS SUBSTRING Function

The SSIS SUBSTRING is a string function which is used to return the part of a given string or character expression that starts at the given index position and get up to the specified length. This article explains how to use the SUBSTRING function with an example and the syntax for extracting a part of a string or character expression is as shown below.

SUBSTRING(<<character_expression>>, <<start>>, <<length>>)
  • Character_expression: Original String or the Column name.
  • Start(integer): The location or the starting position from where the substring will begin extracting the characters from the character_expression.
  • length (integer): The total number of characters you want to extract within the character_expression from the start location.

To demonstrate the SSIS string SUBSTRING 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 SUBSTRING function

For this substring example, 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 SUBSTRING 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 SUBSTRING function to the row cell under the Expression and replace the Character_expression with the string name column, add integers to the start and length arguments. Either you can manually delete the complete <<character_expression>> expression or drag and drop the same or Email column will automatically replace it.

The below expression starts extracting from the fourth location (position) and returns six characters from the fourth position.

SUBSTRING( [Name] , 4, 6 )
SSIS string SUBSTRING Function Expression

The below expression returns eight characters from the Email Address column starting from the tenth position.

SUBSTRING( [Email Adress] , 10, 8 )

In the below code, we used the LEN function to find the length & FINDSTRING to get the location of the @ position. Next, the SSIS SUBSTRING function returns the domain name from the Email address.

SUBSTRING( [Email Adress] , FINDSTRING( [Email Adress] , "@", 1 ) + 1 , 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 SUBSTRING function package to see the part of the whole string (name and email address).

SSIS string SUBSTRING Function output