SSIS – TOKEN Expression

I’ve worked with SSIS for some time now, but a recent question and post pointed out an expression I’d never used before. We often have a need to split strings and pick out some portion of that string. Sometimes we have a need to pull in everything in the “nth” occurrence of a string. The TOKEN expression can be used to get that particular value. I tested this out by mocking up a really simple package.

Start with an empty SSIS package and add a Data Flow Task.

Add a connection to a SQL Server for ease of running a basic SELECT statement to get a test string. In my case, I used “The quick brown fox jumps over the lazy dog.”

Now add a “Derived Column” transform so we can add our expressions for verification. In this case, my expression was pretty basic to test out returning parts of the string, delimited by spaces:
TOKEN(Test,” “,1)

Because this is purely a test package, I created a variable called “RowCount” in order to use the Row Count transform.

I then mapped the output of the Derived Column transform to the Row Count transform.

Finally, to see the results, I added a Data Viewer before the Row Count transform. That left me with a complete Data Flow that looks like this:

And we’re now able to view the results when we Debug the package. Our good ranges from the Derived Column transform result in the words/segments we want to see. When we pass in a value that’s beyond the number of delimiters, we get nothing.

This may not come up too often, but I think this is a useful trick to keep in your SSIS toolkit for cases when you do need to pull out a certain occurrence of a value within a string.