通过 SSIS 获取 SFTP 或 FTPS 文件的最佳方法 [英] Best Method to SFTP or FTPS Files via SSIS

问题描述

这个问题最初问的是在 SSIS 中通过 SFTP 或 FTPS 上传文件的最佳方法是什么.它现在只列出每个解决方案的优缺点.这些天我个人使用 CozyRoc 的 SFTP 库，但我曾经使用过以下每种解决方案.

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 组件库

方法:从 CozyRoc 安装 SSIS 组件库，Codeplex, BizCrypto, PragmaticWorks 或每个开发和生产服务器上的其他供应商，并使用 SFTP 任务上传文件.

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.

优点:易于使用.它看起来、闻起来和感觉上都像一个普通的 SSIS 任务.SSIS 还将密码识别为敏感信息，并允许您使用所有常规选项来保护敏感信息，而不仅仅是以不安全的方式以明文形式存储它.适用于其他 SSIS 任务，例如 ForEach 循环容器.上传和下载失败时出错.当您不知道要下载的远程 FTP 站点上的文件的名称或直到运行时您才知道要上传的文件的名称时，这种方法效果很好.

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.

缺点:除 Codeplex 解决方案外，在生产环境中获得许可需要花钱.需要在每台开发和生产机器上安装库.如果是 Codeplex 解决方案，那么您使用的软件不受任何特定供应商的支持.这也使您依赖供应商在每个版本之间更新他们的库.例如，在 2008 RTM'd 之前，我正在 2008 的 CTP 版本上开发一个新服务器，而 CozyRoc 2005 库与它不兼容.最终他们发布了一个 2008 兼容版本，但我不得不暂时使用命令行解决方案来解决这个问题.

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.

命令行 SFTP 程序

COMMAND LINE SFTP PROGRAM

方法:安装一个免费的命令行 SFTP 应用程序，例如 Putty 和 WinSCP，并通过运行批处理文件或操作系统进程任务来执行它.此处列出了通过 WinSCP 执行此操作的说明.

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.

优点:免费、免费且免费.如果您使用 Putty，您可以确定它是安全的，因为许多 GUI FTP 客户端似乎在幕后使用 Putty.您绝对知道您使用的是 SSH2 而不是 SSH.

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.

缺点:我尝试过的两个命令行实用程序(Putty 和 Cygwin)需要将 SFTP 密码存储在非安全位置.我还没有找到在上传文件时捕获失败或错误的好方法.这个过程看起来和闻起来都不像 SSIS.大多数代码都封装在文本文件中，而不是 SSIS 本身.如果您不知道要上传或下载的文件的确切名称，则很难使用.

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.

第 3 方 C# 或 VB.NET 库

A 3RD PARTY C# or VB.NET LIBRARY

方法:安装 SFTP 或 FTPS 库并使用引用该库的脚本任务来上传文件.(我从来没有试过这个，所以我要猜测一下利弊)

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.

缺点:它是一个结合了 .NET 库的脚本任务.如果您使用的是 SSIS，那么您可能更喜欢 SSIS 任务而不是 .NET 代码.脚本任务也很难排除故障，因为它们没有与常规 .NET 项目相同的调试工具和功能.创建对 3rd 方代码的依赖关系，这在不同版本的 SQL Server 之间可能不起作用.公平地说，与 3rd 方 SSIS 任务库相比，它可能更可能在不同版本的 SQL Server 之间工作.另一个巨大的缺点——到目前为止，我还没有找到一个免费的 C# 或 VB.NET 库.所以如果有人知道，请告诉我！

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!

推荐答案

以下问题可能有用:

推荐选择的 SSIS 组件来执行SFTP 或 FTPS 任务?

Cozyroc:

通过将服务器设置为仅允许 SSHv2"并进行测试，应该很容易测试 ssh 协议的可用性.您试过询问 Cozy 的销售部吗?

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?

命令行sftp:

未知文件名问题可以通过简单的脚本编写/通配符的使用来解决(至少在 Cygwin 下).

The unknown filename problem could be solved simple scripting/use of wildcards (at least under Cygwin).

第三方库:

为什么 FTPS 需要第三方库?.NET 从 2.0 左右开始支持这个协议.

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

这篇关于通过 SSIS 获取 SFTP 或 FTPS 文件的最佳方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！