How to get a file from an FTP site using a stored procedure

Question

I want to create a stored procedure (on SQL Server 2005) that retrieves a file from an FTP site , saves it locally, and then runs an SSIS package to import the contents of the file into a table.

After some suggestions on how to extract the file , the stored procedure is called. Should I use the SQL CLR, invoke the SSIS package that does this, xp_cmdshell or something else?

I would like this process to be as general as possible, so we can use it again and again.

Answer 1

I am the second SSIS route.

, , FTP, , , SSIS. SSIS, , FTP , , , .

Answer 2

SSIS. , , .

, , .