Had another chance to give my new SSIS 2012 presentation this weekend for the fine folks in Atlanta. Everything went pretty well but was surprised when I ended it a bit earlier than I had been when giving it to groups before. Aggravated at myself I was determined to figure out what I was missing. I had been mulling over if I was going over a bit overboard with the portion stepping through tasks in my SSIS packages. I think I had that in the back of my mind giving it as I explained the data flows a bit more loosely than I had been. By doing that I missed the portion allowed me to talk about a couple super lovely new SSIS expression functions: TOKEN and TOKENCOUNT. In order for my conscience to rest I explain them a bit below.
Syntax: TOKENCOUNT(character_expression, delimiter_string) Example: TOKENCOUNT("John,Paul,George,Ringo", ",")
TOKENCOUNT then gives you the number of items that are broken up by your delimiters. The same example above passed to TOKENCOUNT will give you 4…totally helpful to determine how many Beatles you have.
Syntax: TOKEN(character_expression, delimiter_string, occurrence) Example: TOKEN("John,Paul,George,Ringo", ",", 1)
In this example above it will give you “John”. And it’s TOKEN that breaks up the Beatles this time, not Yoko. (So sorry but I couldn’t pass up a joke that cheesy.)
These functions can definitely help out in scenarios where you are breaking out fields in delimited files instead of the FINDSTRING/SUBSTRING ugly gymnastics you normally have to do prior to 2012. Sorry Atlanta for short changing you out of the information.
My slides are available here. The script is available here.
I will be getting out more on the experience of the fan-tabulous event very soon.