如何在 SSIS 脚本任务中修复 NuGet WinSCP.NET? [英] How to fix NuGet WinSCP.NET in SSIS Script Task?

查看：43 发布时间：2021/8/27 20:15:45 c# ssis winscp-net

本文介绍了如何在 SSIS 脚本任务中修复 NuGet WinSCP.NET?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我正在尝试使用 WinSCP.NET NuGet 通过 SSIS 中的脚本任务组件将一些文件上传到 SFTP.在编写代码时一切正常，但是如果在尝试构建之后，WinSCP.NET dll 似乎没有被打破所有引用.

我尝试将 WinSCP 路径添加到我的 PATH 变量(用户).我尝试将 WinSCPNET.dll 的本地版本添加到 GAC.我尝试通过 NuGet 重新安装该软件包.我什至尝试过更改框架版本.

这是我以前在 WinSCP.NET DLL 中遇到的问题.上次我最终使用了一种解决方法，即通过 C# 与命令行交互.但我想使用 DLL，因为它的实现要简单得多.

代码基本上是来自 WinSCP 的样板，有一些细微的变化:

<预><代码>#region 命名空间使用系统；使用 System.Data;使用 Microsoft.SqlServer.Dts.Runtime;使用 System.Windows.Forms;使用 WinSCP；#endregion命名空间 ST_a1d3d6e0b5d54338bce6c79882c303c6{///<总结>///ScriptMain 是脚本的入口点类.不要更改名称、属性、///或此类的父级.///</总结>[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]公共部分类 ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase{#region 帮助:在脚本中使用 Integration Services 变量和参数/* 要在此脚本中使用变量，首先要确保该变量已添加到* 包含在 ReadOnlyVariables 属性中的列表或包含在* 这个脚本任务的 ReadWriteVariables 属性，根据你的* 代码需要写入变量.要添加变量，请保存此脚本，关闭此实例* Visual Studio，并更新 ReadOnlyVariables 和* 脚本转换编辑器窗口中的 ReadWriteVariables 属性.* 要在此脚本中使用参数，请执行相同的步骤.参数始终是只读的.** 从变量中读取的示例:* DateTime startTime = (DateTime) Dts.Variables["System::StartTime"].Value;** 写入变量的示例:* Dts.Variables["User::myStringVariable"].Value = "新值";** 从包参数中读取的示例:* int batchId = (int) Dts.Variables["$Package::batchId"].Value;** 从项目参数中读取的示例:* int batchId = (int) Dts.Variables["$Project::batchId"].Value;** 从敏感项目参数读取的示例:* int batchId = (int) Dts.Variables["$Project::batchId"].GetSensitiveValue();* */#endregion#region 帮助:从脚本触发集成服务事件/* 这个脚本任务可以为了记录目的而触发事件.** 触发错误事件的示例:* Dts.Events.FireError(18, "Process Values", "Bad value", "", 0);** 触发信息事件的示例:* Dts.Events.FireInformation(3, "Process Values", "Processing has started", "", 0, ref fireAgain)** 触发警告事件的示例:* Dts.Events.FireWarning(14, "Process Values", "No values received for input", "", 0);* */#endregion#region 帮助:在脚本中使用 Integration Services 连接管理器/* 在这个脚本任务中可以使用某些类型的连接管理器.看题目*以编程方式使用连接管理器"了解详细信息.** 使用 ADO.Net 连接管理器的示例:* object rawConnection = Dts.Connections["Sales DB"].AcquireConnection(Dts.Transaction);* SqlConnection myADONETConnection = (SqlConnection)rawConnection;*//这里使用一些代码中的连接，然后释放连接* Dts.Connections["销售数据库"].ReleaseConnection(rawConnection);** 使用文件连接管理器的示例* object rawConnection = Dts.Connections["Prices.zip"].AcquireConnection(Dts.Transaction);* string filePath = (string)rawConnection;*//这里使用一些代码中的连接，然后释放连接* Dts.Connections["Prices.zip"].ReleaseConnection(rawConnection);* */#endregion///<总结>///在控制流中执行此脚本任务时调用此方法.///在从该方法返回之前，设置 Dts.TaskResult 的值以指示成功或失败.///要打开帮助，请按 F1.///</总结>公共无效主(){//TODO: 在此处添加您的代码//用户::文件名,$Package::SFTP_HostName,$Package::SFTP_Password,$Package::SFTP_PortNumber,$Package::SFTP_UserNameSessionOptions sessionOptions = 新的 SessionOptions{协议 = 协议.Sftp,HostName = (string)Dts.Variables["$Package::SFTP_HostName"].Value,UserName = (string)Dts.Variables["$Package::SFTP_Password"].Value,SshHostKeyFingerprint = (string)Dts.Variables["$Package::SFTP_Fingerprint"].Value,Password = (string)Dts.Variables["$Package::SFTP_Password"].GetSensitiveValue(),PortNumber = (int) Dts.Variables["$Package::SFTP_PortNumber"].Value,};尝试{使用(会话会话 = 新会话()){//因为 WinSCP .NET 程序集必须存储在 GAC 中才能与 SSIS 一起使用，//您需要明确设置 WinSCP.exe 的路径，//如果使用非默认位置.session.ExecutablePath = (string)Dts.Variables["$Package::WinSCP_Path"].Value;//连接session.Open(sessionOptions);//上传文件TransferOptions transferOptions = new TransferOptions();transferOptions.TransferMode = TransferMode.Binary;TransferOperationResult transferOperationResult = session.PutFiles((string)Dts.Variables["User::FileName"].Value, (string) Dts.Variables["$Package::SFTP_RemoteFileName"].Value,真，转移选项)；//抛出任何错误transferOperationResult.Check();//打印结果bool fireAgain = false;foreach (TransferEventArgs transferEvent in transferOperationResult.Transfers){Dts.Events.FireInformation(0, null,string.Format("{0} 上传成功", transferEvent.FileName),null, 0, ref fireAgain);}}}捕获(例外 e){Dts.Events.FireError(0, null,string.Format("使用 WinSCP 上传文件时出错:{0}", e),空, 0);Dts.TaskResult = (int)DTSExecResult.Failure;}Dts.TaskResult = (int)ScriptResults.Success;}#region ScriptResults 声明///<总结>///这个枚举在这个类的范围内提供了一个方便的速记，用于设置///脚本的结果.//////此代码是自动生成的.///</总结>枚举脚本结果{成功 = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success，失败 = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure};#endregion}}

这应该按原样编译，并允许我运行 SSIS，上传文件.相反，引用中断了，我收到了很多缺失的引用错误:

<块引用>

错误 CS0246:找不到类型或命名空间名称WinSCP"(您是否缺少 using 指令或程序集引用?)

错误:此项目引用了此计算机上缺少的 NuGet 包.使用 NuGet Package Restore 来下载它们.有关详细信息，请参阅 http://go.microsoft.com/fwlink/?LinkID=322105.丢失的文件是 ..\packages\WinSCP.5.15.0\build\WinSCP.targets.

解决方案

我确实可以重现您的问题，当我使用 WinSCP NuGet 包.看起来 NuGet 包管理器和 SQL Server Data Tools 之间存在问题.错误所指的文件确实存在(在相对于脚本任务 .csproj 文件的路径中).

实际上，看起来甚至不建议在 SSIS 中使用 NuGet.您应该将程序集注册到 GAC:

<小时>

事实上，如果我按照 WinSCP 说明使用 SSIS 中的程序集(使用GAC)，它工作得很好.

确保您已卸载 NuGet 包.
将 WinSCPnet.dll 安装到 GAC 或订阅AppDomain.AssemblyResolve 事件.
并将 WinSCPnet.dll 添加到您的脚本任务项目中.

I'm trying to use the WinSCP.NET NuGet to upload some files to an SFTP through a Script Task component in SSIS. While writing the code everything went fine, but if after attempting to build, the WinSCP.NET dll seems to not be picked up breaking all of the references.

I've tried adding WinSCP path to my PATH variable (user). I've tried to add the local version of the WinSCPNET.dll to the GAC. I've tried to reinstall the package through NuGet. I've even tried to change the framework versions.

This is a problem I've had before with the WinSCP.NET DLL. Last time I ended up using a workaround by interfacing with the command line through C#. But I would like to use the DLL, as it's a much simpler implementation.

The code is basically the boilerplate from WinSCP, with some minor changes:


#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using WinSCP;
#endregion

namespace ST_a1d3d6e0b5d54338bce6c79882c303c6
{
    /// <summary>
    /// ScriptMain is the entry point class of the script.  Do not change the name, attributes,
    /// or parent of this class.
    /// </summary>
    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {
        #region Help:  Using Integration Services variables and parameters in a script
        /* To use a variable in this script, first ensure that the variable has been added to 
         * either the list contained in the ReadOnlyVariables property or the list contained in 
         * the ReadWriteVariables property of this script task, according to whether or not your
         * code needs to write to the variable.  To add the variable, save this script, close this instance of
         * Visual Studio, and update the ReadOnlyVariables and 
         * ReadWriteVariables properties in the Script Transformation Editor window.
         * To use a parameter in this script, follow the same steps. Parameters are always read-only.
         * 
         * Example of reading from a variable:
         *  DateTime startTime = (DateTime) Dts.Variables["System::StartTime"].Value;
         * 
         * Example of writing to a variable:
         *  Dts.Variables["User::myStringVariable"].Value = "new value";
         * 
         * Example of reading from a package parameter:
         *  int batchId = (int) Dts.Variables["$Package::batchId"].Value;
         *  
         * Example of reading from a project parameter:
         *  int batchId = (int) Dts.Variables["$Project::batchId"].Value;
         * 
         * Example of reading from a sensitive project parameter:
         *  int batchId = (int) Dts.Variables["$Project::batchId"].GetSensitiveValue();
         * */

        #endregion

        #region Help:  Firing Integration Services events from a script
        /* This script task can fire events for logging purposes.
         * 
         * Example of firing an error event:
         *  Dts.Events.FireError(18, "Process Values", "Bad value", "", 0);
         * 
         * Example of firing an information event:
         *  Dts.Events.FireInformation(3, "Process Values", "Processing has started", "", 0, ref fireAgain)
         * 
         * Example of firing a warning event:
         *  Dts.Events.FireWarning(14, "Process Values", "No values received for input", "", 0);
         * */
        #endregion

        #region Help:  Using Integration Services connection managers in a script
        /* Some types of connection managers can be used in this script task.  See the topic 
         * "Working with Connection Managers Programatically" for details.
         * 
         * Example of using an ADO.Net connection manager:
         *  object rawConnection = Dts.Connections["Sales DB"].AcquireConnection(Dts.Transaction);
         *  SqlConnection myADONETConnection = (SqlConnection)rawConnection;
         *  //Use the connection in some code here, then release the connection
         *  Dts.Connections["Sales DB"].ReleaseConnection(rawConnection);
         *
         * Example of using a File connection manager
         *  object rawConnection = Dts.Connections["Prices.zip"].AcquireConnection(Dts.Transaction);
         *  string filePath = (string)rawConnection;
         *  //Use the connection in some code here, then release the connection
         *  Dts.Connections["Prices.zip"].ReleaseConnection(rawConnection);
         * */
        #endregion


        /// <summary>
        /// This method is called when this script task executes in the control flow.
        /// Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
        /// To open Help, press F1.
        /// </summary>
        public void Main()
        {
            // TODO: Add your code here
            // User::FileName,$Package::SFTP_HostName,$Package::SFTP_Password,$Package::SFTP_PortNumber,$Package::SFTP_UserName
            SessionOptions sessionOptions = new SessionOptions
            {
                Protocol = Protocol.Sftp,
                HostName = (string)Dts.Variables["$Package::SFTP_HostName"].Value,
                UserName = (string)Dts.Variables["$Package::SFTP_Password"].Value,
                SshHostKeyFingerprint = (string)Dts.Variables["$Package::SFTP_Fingerprint"].Value,
                Password = (string)Dts.Variables["$Package::SFTP_Password"].GetSensitiveValue(),
                PortNumber = (int) Dts.Variables["$Package::SFTP_PortNumber"].Value,
            };

            try
            {
                using (Session session = new Session())
                {
                    // As WinSCP .NET assembly has to be stored in GAC to be used with SSIS,
                    // you need to set path to WinSCP.exe explicitly,
                    // if using non-default location.
                    session.ExecutablePath = (string)Dts.Variables["$Package::WinSCP_Path"].Value;

                    // Connect
                    session.Open(sessionOptions);

                    // Upload files
                    TransferOptions transferOptions = new TransferOptions();
                    transferOptions.TransferMode = TransferMode.Binary;

                    TransferOperationResult transferOperationResult = session.PutFiles(
                        (string)Dts.Variables["User::FileName"].Value, (string) Dts.Variables["$Package::SFTP_RemoteFileName"].Value, 
                        true, transferOptions);

                    // Throw on any error
                    transferOperationResult.Check();

                    // Print results
                    bool fireAgain = false;
                    foreach (TransferEventArgs transferEvent in transferOperationResult.Transfers)
                    {
                        Dts.Events.FireInformation(0, null,
                            string.Format("Upload of {0} succeeded", transferEvent.FileName),
                            null, 0, ref fireAgain);
                    }
                }
            }
            catch (Exception e)
            {
                Dts.Events.FireError(0, null,
                    string.Format("Error when using WinSCP to upload files: {0}", e),
                    null, 0);

                Dts.TaskResult = (int)DTSExecResult.Failure;
            }

            Dts.TaskResult = (int)ScriptResults.Success;
        }

        #region ScriptResults declaration
        /// <summary>
        /// This enum provides a convenient shorthand within the scope of this class for setting the
        /// result of the script.
        /// 
        /// This code was generated automatically.
        /// </summary>
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

    }
}

This should compile as is and allow me to run the SSIS, to upload the file. Instead the references break and I receive a lot of missing reference errors:

Error CS0246: The type or namespace name 'WinSCP' could not be found (are you missing a using directive or an assembly reference?)

Error: This project references NuGet package(s) that are missing on this computer. Use NuGet Package Restore to download them. For more information, see http://go.microsoft.com/fwlink/?LinkID=322105. The missing file is ..\packages\WinSCP.5.15.0\build\WinSCP.targets.

解决方案

I can indeed reproduce your problem, when I use WinSCP NuGet package. It looks like a problem between the NuGet package manager and SQL Server Data Tools. The file the error refers to actually does exist (in a path relative to the script task .csproj file).

Actually, it looks like it's not even recommended to use NuGet in SSIS. You should rather register the assembly to GAC:

And indeed, if I follow the WinSCP instructions for using the assembly from SSIS (using the GAC), it works just fine.

Make sure you have uninstalled the NuGet package.
Install WinSCPnet.dll to GAC or subscribe AppDomain.AssemblyResolve event.
And add WinSCPnet.dll to your script task project.

这篇关于如何在 SSIS 脚本任务中修复 NuGet WinSCP.NET?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文

如何在 SSIS 脚本任务中修复 NuGet WinSCP.NET? [英] How to fix NuGet WinSCP.NET in SSIS Script Task?

问题描述

相关文章

C#/.NET最新文章

热门教程

热门工具

登录关闭

如何在 SSIS 脚本任务中修复 NuGet WinSCP.NET? [英] How to fix NuGet WinSCP.NET in SSIS Script Task?

问题描述

相关文章

C#/.NET最新文章

热门教程

热门工具

登录 关闭

登录关闭