We have a configuration where our SQL Server reads files that were FTPed to us. We have them on separate servers and I'd recommend the same thing to you. I would never want to have them on the same server.

As for as SQL retrieving data from an FTP server, I see two approaches you can take. Both can be done in a stored procedure.

1. You can have SQL Server read from a network share where the incoming files are saved. The requirement here is that the SQL Server (the network account used by the service) has to have permission to the particular network share.

2. You could have SQL Server access the FTP server using the FTP protocol and do a GET to download the files to its own file system and process them from there. This approach has the added advantage of being able to process files from an external FTP server you don't control.