Thanks Jeff for your solution.

Isn't there a way to get this through without using linked server? A little simpler solution?

Please let me know.

Thank you Jeff

how do I set the variables for the FTP task?

How to assign values to the variables at run time?

All the ftp details are variables that get data from a database table and loop through the resultset.

How to download using .NET code. I have "ftpservername", "ftpdirectory" and the "filename" details.

I need to copy this file to my local server.

I am familiar with SSIS, but not sure of how to get this task done.

The requirement is such. What will be the solution using SSIS, so that it can be considered. please let me know.

I have a constraint that I cannot use SSIS. Can this be possible with just sql?

So you mean to say that adding n number of lookup components for each lookup column is fine?

Can you tell me if there would be any impact on the processing...