c# - 如何修复 SSIS 脚本任务中的 NuGet WinSCP.NET？

Question

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

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

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

代码基本上是 WinSCP 的样板，有一些小的变化:

#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

    }
}

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

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.

Answer 1

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

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

• How can I use NuGet with SSDT?
• Creating a reference to a custom assembly from an SSIS Script Task - vb
• SSIS Script Task cant find reference to assembly

---

事实上，如果我遵循 WinSCP instructions for using the assembly from SSIS (使用 GAC)，它工作得很好。

• 确保您已卸载 NuGet 包。
• Install WinSCPnet.dll to GAC或 subscribe AppDomain.AssemblyResolve event .
• 并将WinSCPnet.dll 添加到您的脚本任务项目中。