SSIS TOKEN Function

The SSIS TOKEN is a string function to find and return the specified occurrence of a token in a character expression or string. A token may be a delimiter in a given set of delimiters. This article explains how to use the SSIS TOKEN function with an example and the syntax for finding the token and returning the part of a string before the token is as shown below.

TOKEN(<<Character_expression>>, <<delimiter_expression>>, <<occurrence>>)
  • Character_expression: Original String or the Column name where the search for delimiter happens.
  • delimiter_expression: Search for this delimiter within the character_expression.
  • Occurrence: Its an integer value that decides which occurrence the TOKEN function has to consider.

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

For this TOKEN example, 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 TOKEN 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 TOKEN function to the row cell under the Expression and replace the Character_expression with the Name (string) column, delimiter as space and the occurrence as 1. Either you can manually delete the complete <<Character_expression>> expression or drag and drop the Name column will automatically replace it.

TOKEN( [Name] , " ", 1 )

TOKEN( [Name] , " ", 2 )

TOKEN( [Name] , " ", 3)

TOKEN( [Phone] , "-", 1 )

TOKEN( [Phone] , "-", 2 )
SSIS TOKEN Function Expression

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 TOKEN function package to see the part. Of a string from the name and Phone fields.

As we all know, or all the expressions against the name field, we used the space as the delimiter and for the Phone field, we sued the “-” as the delimiter.

  1. NAME1 – It returns the first part of the Name field because we choose the first occurrence.
  2. NAME2 – It shows the second part of the Name field because we choose the second occurrence.
  3. NAME3 – It shows the third part of the Name field.
  4. PH1 – It shows the first part of the Phone number that is before the first occurrence of the “-” symbol.
  5. PH2 – t shows the second part of the Phone number that is before the second occurrence of the “-” symbol.
SSIS TOKEN Function output