Best Method to SFTP or FTPS Files via SSIS
This question originally asked which is the best method for uploading files via SFTP or FTPS in SSIS. It now just lists the pros and cons of each solution. I personally use CozyRoc's SFTP library these days, but I've used each of the below solutions at one point or another.
SSIS COMPONENT LIBRARY
Method: Install a SSIS component library from CozyRoc, Codeplex, BizCrypto, PragmaticWorks, or some other vendor on each development and production server and use the SFTP task to upload the files.
Pros: Easy to use. It looks, smells, and feels like a normal SSIS task. SSIS also recognizes the password as sensitive information and allows you all the normal options for protecting the sensitive information instead of just storing it in clear text in a non-secure manner. Works well with other SSIS tasks such as ForEach Loop Containers. Errors out when uploads and downloads fail. Works well when you don't know the names of the files on the remote FTP site to download or when you won't know the name of the file to upload until run-time.
Cons: With the exception of the Codeplex solution, this costs money to license in a production environment. Requires installing the libraries on each development and production machine. If it is the Codeplex solution, then you are using software that isn't supported by any specific vendor. This also makes you dependent upon the vendor to update their libraries between each version. For instance, before 2008 RTM'd, I was developing a new server on a CTP version of 2008 and the CozyRoc 2005 library was incompatible with it. Eventually they released a 2008 compatible version, but I had to temporarily use the command line solution to work around this issue.
COMMAND LINE SFTP PROGRAM
Method: Install a free command-line SFTP application such as Putty and WinSCP and execute it either by running a batch file or operating system process task. Instructions for doing this via WinSCP are listed here.
Pros: Free, free, and free. You can be sure it is secure if you are using Putty since numerous GUI FTP clients appear to use Putty under the covers. You DEFINATELY know you are using SSH2 and not SSH.
Cons: The two command-line utilities I tried (Putty and Cygwin) required storing the SFTP password in a non-secure location. I haven't found a good way to capture failures or errors when uploading files. The process doesn't look and smell like SSIS. Most of the code is encapsulated in text files instead of SSIS itself. Difficult to use if you don't know the exact name of the file you are uploading or downloading.
A 3RD PARTY C# or VB.NET LIBRARY
Method: Install a SFTP or FTPS library and use a Script Task that references the library to upload the files. (I've never tried this, so I'm going to guess at the pros and cons)
Pros: Probably easy to capture errors. Should work well with variables, so it would probably be easy to use even when you don't know the exact name of the file you are uploading or downloading.
Cons: It's a script task combined with .NET libraries. If you are using SSIS, then you probably are more comfortable with SSIS tasks then .NET code. Script tasks are also difficult to troubleshoot since they don't have the same debugging tools and features as regular .NET projects. Creates a dependency on 3rd party code that may not work between different versions of SQL Server. To be fair, it is probably MORE likely to work between different versions of SQL Server than a 3rd party SSIS task library. Another huge con -- I haven't found a free C# or VB.NET library that does this as of yet. So if anyone knows of one, then please let me know!
Answer
The following question might be of use:
What would be a recommended choice of SSIS component to perform SFTP or FTPS task?
Cozyroc:
It should be easy to test ssh protocol availability by setting the server to "allow only SSHv2" and testing. Have your tried asking Cozy's sales dept?
Command line sftp:
The unknown filename problem could be solved simple scripting/use of wildcards (at least under Cygwin).
3rd party lib:
Why do you need a third party lib for FTPS? .NET has supported this protocol since 2.0 or so.
http://msdn.microsoft.com/en-us/library/system.net.ftpwebrequest.enablessl.aspx