Whilst recently compiling a reasonably complex transformation to bring in some data from an Excel spreadsheet I was left in the situation where I had a some look ups that were failing to resolve due to case sensitivity. This was a failure on my part really as I should have understood from the outset that this could have been the case and I should have factored this in from the start but I didn’t. No problem usually, You can just change the case at the source and and in the look-up component and you’re there. However… In my situation this was not feasible without me throwing myself out of the nearest window and making finger paintings with my own blood in my dying moments. The reason for this is that I had about 50 separate sources which is an onerous task in itself. In addition to this as I was ‘unioning’ all of this data together using the union component and then going through several more complex transformations with additional unions along the way it was getting very messy, The union components do not refresh with new fields when the source changes and so the only way to correct this is to drag new union components on in place of the old ones! This in turn means that all of the adjoining components also need to be refreshed and even talking about it I can feel the end approaching.
So how to save a life……
Firstly I altered my look-up components (SQL Server) to be case insensitive using standard TSQL like so:-
SELECTÂ LOWER(ResponseLookUp),ResponseLookUpIDÂ FROM ResponseLookUp
Next however I had to change the source data coming from the Excel spreadsheets. Enter a new transformation component (to me…) The CharacterMap
We configure this little fellow thus:-
Basically I have 7 questions that need to be looked up using case insensitive searching so for each question (1 to 7):-
1. Configure a transformation to make an in-place change, that is the contents of the original field will be changed (Remember, I had a complex transformation already and didn’t want to change it as much as was possible!!)
2. The ‘operation’ we select in this instance is to Lowercase the entire question.
And that is it, When I now run the transformations hey presto…. no more failures in the look up transformations. I am now free to enjoy the children’s finger painting in a much more traditional media
I hope that you will all find this snippet as helpful as I did.