I agree that you should pay attention to leading and trailing spaces, in particular trim all of your text type columns. Where SQL Server will usually ignore those spaces, SSIS is basically a .Net product and much pickier about these sorts of things. I make it a rule to trim pretty much all text data regardless of source, and that would include trimming the query that loads your lookup as well.