The SSIS TOKENCOUNT is a string function to find and return the total number of tokens available in a character expression or string. A token may be marked by a delimiter in a given set of delimiters. This article explains how to use the TOKENCOUNT function with an example and the syntax to count the total number of token available in a string is as shown below.

TOKENCOUNT(<<Character_expression>>, <<delimiter_expression>>)

Character_expression: Original String or the Column name where the token search happens.

delimiter_expression: Search the character_expression for this delimiter.

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


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

The below two expression will count the total number of times the bank spaces toke reported in name column and – symbol in Phone column

TOKENCOUNT( [Name] , " " )

TOKENCOUNT( [Phone] , "-" )
SSIS TOKENCOUNT 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 TOKENCOUNT function package to see the black space and – token count in the name and Phone fields.

SSIS TOKENCOUNT Function output