The other day, i was working on a new SSIS (SQL Server Integration Services) package to transform data from a production database to an analysis database. It worked quite well, i thought, everything seemed to work as it was supposed to when testing with a subset of data. So next up, live usage. And that was the moment the shit hit the fan.

All of a sudden the package began raising errors because a critical lookup was failing and hence it didn’t find records.
The lookup task had to query a table containing string values and compare them to given values, and while SQL is (normally) case-insensitive the lookup component seems to be case sensitive. Speaking about finding out the hard way.

After some more reading, it seems this case-sensitivity is only active when using the ‘full-cached’ operating mode.