hello, i want to do a lookup using following logic, let me know how if i can achieve it? I have a table called tblLookUpValue and tblData. I want to search lookup_value from tblLookUpValue in desc filed of tblData, if found then return id from tblLookUpValue table. tblLookUpValue lookup_value nvarchar(10),lookup_id int -- sample data '123', 1 '111',2 '333',3 tblData desc nvarchar(200) --sample data 'Youareawsome123' 'you123areawsome' 'youyouyou111' 'youare333isawsome' hope it is clear? mark it as answer if it answered your question :)

You can use advance tab of look where you can customize your query, Somthing like shown in following example, http://www.julian-kuiters.id.au/article.php/ssis-lookup-with-range You can use "LIke" T SQL here. Other option is if you have int value in betweeen Desc filed you can use pat index command and get that partiiculer code out of desc filed and than do a look up on it, http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/ca402c94-50fc-4206-b687-84c601551b6d/ Gaurav Gupta http://sqlservermsbiblog.blogspot.com/

If your tables are in the same DB I would advise you to use a query and get the output in the oledb source itself by using like command and then join. But I see this is prone to bug: What would be the output if the value is - 'you11123areawsome' or can u never have such a scenario?? Tweet me..

Hey Sudeep, I thought of that but it will never be a scenario. So what is the best solution? Thanks, Pmark it as answer if it answered your question :)

Also, let you guys know I'm using odbc connection via ado.net so if something rquired oledb, it would not be possible to use :(mark it as answer if it answered your question :)

in that case I would not go for lookup rather as said earlier would modify my source query to get the lookup values in the OLEDB Source itself. I have not worked on ODBC connection so am not sure if this will work. Tweet me..

Situation is a bit difficult, if your data was on same server, Sudeep suggestion was best, because you can join tables with t-sql more efficiently. you need to clarify more your logic to make it simpler. does your lookup table values only contain numeric data? I mean that can you fetch only numeric part of data table and then join columns together?http://www.rad.pasfu.com