I have used tsql join in my ETL transformation, due to complexity and easy maintainence of my ETL script. I understand that SSIS look up function is good in terms of lookup for surrogate key from dimension table.
However, I got complex business rule where I had to translate my lookup table with customize view such as use of select statement to filter the row I need.
What is the best approach in terms lookup tsql or SSIS?
Thanks in advance