Hello Sachin.

Sounds like your solution works very well.

Did you consider creating a transformation where you transform the multiple columns into say two or three columns, so you would have just one lookup component? I believe the advantage of this approach would be that you would think you would be able to process lookups against a larger set of values by letting SSIS take care of the caching, or by turning off the caching option in SSIS.

Your original table structure would be something like:

PK course_1, course_2, course_3, ...

1, a, b, c

Your new table structure would end up being something like:

PK, column_name, column_value

1, course_1, a

1, course_2, b

1, course_3, c

You could easily process the data in this format and provide the desired output into your two tables. I would be curious to know if this approach ends up being any faster or slower in your case.

If you don't know how to perform that kind of a transformation, I will be happy to point you in the correct direction.

Thanks

Rob