Token function in SSIS expression

Returns a token (substring) from a string based on the specified delimiters that separate tokens in the string and the number of the token that denotes which token to be returned. The function returns a substring of the provided string. The substring is defined in the following way: we define the character expression and the delimiter string that will divide the character expression into many substrings or tokens. The result of the function will be the substring whose position is defined by the occurrence number. 

Syntax

TOKEN(character_expression, delimiter_string, occurrence)

The arguments of the TOKEN function are:

Ø  Character_expression – represents the string where we will search the string we are looking for. 

Ø  Delimiter_string – represents the string that holds one or more delimiters.

Ø  Occurrence – represents the number of the token that will be returned.

Let’s see an example

Suppose we have a excel file and a column (State name) have the value with comma separated and we need to load state code in one column and state name in another column.

State name

Bihar ,BR

Utter Pradesh ,UP

Maharashtra ,MH

Jharkhand ,JH

We want to load this as below

State Name	State code
Bihar	BR
Uttar Pradesh	UP
Maharashtra	MH
Jharkhand	JH

There are multiple ways to get this result.

The easy way using token function.

Taking data flow task and source as  excel file.

In drive column I am using the Token function. We know that that before comma is the state name and after it states code.

  

To see the result I am taking Multicast.

  

If the value of <occurrence> parameter is higher than the total number of tokens in the string, the function returns NULL.

Let’s see in the above example

  

Column has only 2 comma-separated value. Occurrence I am using 5 so it will return a null value.

  

TOKEN returns a null result if the character_expression is null.

TOKEN works only with the DT_WSTR data type. A character_expression argument that is a string literal or a data column with the DT_STR data type is implicitly cast to the DT_WSTR data type before TOKEN performs its operation. Other data types must be explicitly cast to the DT_WSTR data type.