I am pretty new to SSIS and below is the SQL query which explains what I am trying to do:
select * from Table1 t1 join (Select ID, LEN(ID) as len from Table2) as t2 on RIGHT(RTRIM(t1.col) , t2.len) = t2.ID
There might be some better query in SQL for the same but I am intrested as how to implement this logic in SSIS?
I need to find all the records from table 1 where the col (from the right) matches with the ID value retrieved from table 2.
The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:
SELECT * FROM Table1 t1 JOIN ( SELECT Table2.ID, LEN(Table2.ID) AS len FROM Table2 ) AS t2 ON RIGHT(RTRIM(t1.col), t2.len) = t2.ID