hi,

i want to use lookup transformation using Excel as a source .

i am having two excel files .

file1 one of the column contains 'Andhrapradesh'

file2 one of the column contains 'ap'

here want to match these using l

Do not you have another column to relate between tables? If so, you probably need to use ROW_NUMBER function to create a surrogate key and then JOIN between tables..

Hi Gvrspk,

Check the below link..

http://www.sqlhub.com/2010/07/lookup-transformation-task-in-ssis-2008.html

Or Else Follow the below steps.

1. Drag your source component,

2. Add a lookp up component..

3. Connect source to lookup..

4. Rightclick---->edit lookup Component--->On General tab -->select fullcahe for cache mode ---> select oledb connection manager ---> select Redirect rows to no match output on Speficy how to handle with no match entries on dropdown..

5.Now go to Connection manager tab --> select connection of you lookup table--> select table for lookup  your base table.

6.Now go to Columns tab ---> You can see two boxes with your source columns and lookup table columns..

--->rightclick on source coulmn edit mappings (select column in source and lookup)

7. Click ok

8. Add a destination in to control folw---> connect the lookup output to destination.. while doing so it will gives a dropdown of matchout and no match output

9.Match output will be your duplicate rows , no matchoutput is your final records to load in to destination table..