hi all, for couple of months i have been working as ssis, ssrs developer and i belive there is so much to learn. my question is, when you come across a case where you need lookup a table and get some data from the lookup table, we use look up transformation. instead what i do is just use a execute Sql task and write a join query and get whatever i want, also when u have to join soo many table i just use my execute sql task and join and get watever i want. can someone please suggest me what are advantages and disadvantages in my approch?

If the data you are trying to 'lookup' is all within reach of your base table, then yes, a SQL JOIN statement is by far the best way to go. But what if you are loading data from Excel or a flat file and your lookup data is on AS400 or Oracle or some other proprietary system? I suppose you could write OPENQUERY statements, but not all systems support that (txt files). SSIS is a great tool when data is scattered across systems and needs to be brought together in a coherent way. If ALL the data was stored on a SQL database, us SSIS Developers would be out of a job!Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.

thanks a lot