You already know that SSIS 2005 has more issues than SSIS 2008 version. I have also came across an issue regarding Look-up Task.

When you use Look-up Task in your dataflow activity. Sometime it runs and sometimes it fails. Well, there could be many reasons, but we will discuss about one of the reason that is related to the “caching”. Look-up uses caching for fast comparision of values.

So, you might have faced something like this. You have configured look-up correctly and when you run, you will see following 🙁

And now you are wondering.. no problem with connections or mapping of columns. But how come it still fails.

Well, you can find the reason if you check the property of that look-up task. Select Look-up task and go to its properties. Check “Custom Properties” section. You will find property called “CacheType”. If the value of this property is set to “Full” that means … this is the reason why look-up failed. The reason is… your look-up task has cahced the values for comparisions and later it didnt get refreshed… so values become invalid for some look-up records.

So, to overcome this problem, change the value to “None” for CacheType property.

Once you make it as None, you will notice that Look-up task displays error indication. i.e. there is something wrong with the task.

You just need to refresh the task. So, to refresh it, double click the look-up task, click on columns tab and close.

Now, run it. Apart from caching, if you have configured your look-up task correctly then it will run properly without any problem.

i hope this helps you guys somewhere.

Thanks, Khilit