This blog has been moved to: http://sqlblog.com/blogs/jorg_klein/
This blog post has been moved to: http://sqlblog.com/blogs/jorg_klein/archive/2009/04/14/ssis-lookup-is-case-sensitive.aspx
Jorg Klein has just written a useful blog entry about the SSIS Lookup component entitled "SSIS – Lookup
Really interesting blog Jorg! I'll certainly keep this in mind!
I also ran into this problem and after reading this post and others I was able to solve my problem. I went to the advanced tab and Checked Enable Memeory Restriction and Modify SQL Statement. Mapped the parameter and I was fine. It may still be hitting the DB once per lookup, but at least the lookup is working. I had tried using Upper in both my Lookup Query and Source Query and I still encountered the problem. Another "solution" I first used in this situation was to use a Fuzzy Lookup with a min similarity of 1 and I got the matches I needed.
The SQL Server Integration Services team added valuable new caching options (and scalability) to the
Space Sensitivity in SSIS Lookups
Pingback from SSIS – Data Flow Transformations « Systems Engineering and RDBMS
Learn more about Lookup and its new features: A compilation
Pingback from Space Sensitivity in SSIS Lookups « Tim Mitchell's SQL Server BI Blog
Pingback from Space Sensitivity in SSIS Lookups | Tim Mitchell