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.
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.
Welcome to the blogging and SSIS community, Chris!
Yes – they should find some way to “advertise” that limitation more perdominantly. It comes as quite a surprise – especially when it only occurs in cached mode. It makes sense once you start thinking of the cached mode as a Hashtable – which wouldn’t support case-insensitive lookups unless coded specifically to do so. Just FYI – the cached Lookup is also space-sensitive and collation-sensitive. The uncached Lookup leaves the comparison up to the database engine (whatever one that is) – but the cached Lookup compares it internally, very strictly.