Sunday, 1 March 2020

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.

No comments:

Post a Comment

If you have any doubt, please let me know.

Popular Posts