Best Method to SFTP or FTPS Files via SSIS

Question

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!

Solution 1

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

Solution 2

I just wanted to provide an update on what we actually decided to do to resolve the SFTP issues in SSIS. Here's the breakdown of what happened:

1. I initially tried using Putty and some batch files to upload files, but it was difficult to capture errors. Also, I was storing our SFTP credentials in clear text files since it was part of the Putty upload scripts.

2. We purchased a CozyRoc license for our SSIS server for a couple hundred dollars a year and I'm completely satisified with the results of using their product. With CozyRoc's product, the control flow task raises errors if there are any problems with the upload. Since I also have several junior SSIS programmers on my team, it was easier for them to understand how to setup the control flow task than to use the Putty scripts method. And finally, the password is encrypted using SSIS's native encryption for protecting sensitive data. I no longer have any passwords stored in clear text on my server.

I did review some of the other 3rd party libraries that were recommended in this question, but it appears CozyRoc was the cheapest of the vendors and they also had a few other SSIS tasks that I have been able to use in my BI team. Thanks, CozyRoc!