通过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 ， 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之前，我在CTP版本2008上开发了一个新服务器，而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程序

方法:安装免费的命令行SFTP应用程序(例如Putty和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.

3RD PARTY 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项目相同的调试工具和功能.创建对第三方代码的依赖关系，该依赖关系在不同版本的SQL Server之间可能无法正常工作.公平地说，与第三方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屋！