The Lookup Transform does case sensitive string comparisons. This means that you need to a little bit of special handling to get it to work in a case insensitive way.
In most cases (and especially if you're using a case sensitive collation on the database/table that holds your reference data), you'll want to add an upper case version of your index column(s) to your data flow (or replace your current column(s) if you don't need it later downstream). This can be done directly at the source, through a Character Map transform, or a Derived Column transform (both transforms should perform about the same).
You'll have to use the UPPER() function on the columns in your lookup transform's SQL statement as well to get them to match. You'll want to use a Full cache mode here, because using a function (like UPPER()) on your index columns will prevent the database from using any indexes it has created on the column.
This is a case where you can also use the new cache connection manager to pre-create your case insensitive cache.
If your lookup columns are indexed, your database has a case insensitive collation, and you're not going to be hitting a large percentage of your reference table, you might want to take an alternate approach. Switch the cache mode to Partial and remove the UPPER() function from your SQL statement in the Lookup. This will allow the query engine to make use of your table indexes.
Other Resources
See a related post and discussion about case insensitive lookups on Jamie Thomson's blog .