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.
I’ve been working with SQL Server for quite some time. Along the way, I’ve learned quite a few things and realized that I won’t ever know everything about SQL Server. I intended to keep growing and learning to be able to do my job well and share my experiences with others.
I currently work for a Health-related non-profit based in Boise as a Database Architect.