So I’ve discovered another benefit of being a technical blogger. Not only do you get some kudos when you write something that helps someone else, but if you offer up a less-than-optimal solution, you’ll get some suggestions on how it can be done better. I’ve had my share of the former, but earlier this week I experienced the latter.
Last month I blogged about using the SSIS CODEPOINT() function to make it easier to split data streams based on a character value. Although the method I suggested gets the job done, of my blog readers wisely pointed out to me that you can do range comparisons on character data using the SSIS expression language. So, rather than doing a greater/less than comparison on the ASCII values of each of the significant comparison characters (the first letter, or perhaps the first two letters if you’re splitting deeper than just the first letter), you can simply run an alphabetical comparison, such as the following:
As you can see, the syntax and complexity of this solution is much simpler than what I proposed using CODEPOINT(). Thanks to Hrvoje Piasevoli for pointing out a better way to do this.