I want to create a stored procedure (on SQL Server 2005) that fetches 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.
I'm after some suggestions on how to fetch the file by calling a stored procedure. Should I use SQL CLR, call an SSIS package that does it, xp_cmdshell, or something else?
I'd like this process to be as generic as possible, so we can use it over and over again.
I would suggest that you pursue the SSIS route. All the components and technologies that you would need are already created for you to use.
You could also add a layer of validation, data transformations prior to importing the data into your database should you wish.
I second the SSIS route.
Anything you save by making a generic FTP routine is great, but unless all your files are the same layout, you will not be able to easily handle the importing of differing files with a single re-useable SSIS package anyway. You can handle all the error handling and logging in SSIS and you won't have to worry about handling the FTP outside and handling errors there and then if that's successful going to the import package where you will already have to handle any errors there anyway.