SSIS C#脚本任务上传CSV至SFTP报错及批量上传需求

问题背景 #

尝试通过SSIS的C#脚本任务将本地网络目录下所有.csv文件上传至SFTP服务器，脚本编译无报错，但运行时提示“Exception has been thrown by the target of an invocation.”且无日志输出。需先解决单个文件上传的报错问题，再实现批量上传功能。

报错的单个文件上传脚本 #

[SSISScriptTaskEntryPoint]
public partial class ScriptMain : VSTARTScriptObjectModelBase
{
    public void Main()
    {
        // Setup session options
        SessionOptions sessionOptions = new SessionOptions
        {
            Protocol = Protocol.Sftp,
            // To setup these variables, go to SSIS > Variables.
            // To make them accessible from the script task, in the context menu of the
            // task, choose Edit. On the Script task editor on Script page,
            // select ReadOnlyVariables, and tick the below properties.
            HostName = (string)Dts.Variables["$Project::prmHost"].Value,
            UserName = (string)Dts.Variables["$Project::prmUser"].Value,
            Password = (string)Dts.Variables["$Project::prmPassword"].Value,
            SshHostKeyFingerprint = (string)Dts.Variables["$Project::prmFingerprint"].Value,
            PortNumber = Convert.ToInt32(Dts.Variables["$Project::prmPort"].Value)
        };

        try
        {
            using (Session session = new Session())
            {
                // If WinSCP .NET assembly has been stored in GAC to be used with SSIS,
                // you need to set path to WinSCP.exe explicitly.
                // This is not needed if you have subscribed AppDomain.AssemblyResolve event
                // and the WinSCP.exe is in the same location as WinSCPnet.dll.
                //session.ExecutablePath = @"C:\winscp\winscp.exe";
                session.SessionLogPath = Dts.Variables["$Project::prmLogFileName"].Value.ToString();

                // Connect
                session.Open(sessionOptions);

                string remotePath = Dts.Variables["$Project::prmPath"].Value.ToString();
                string localPath = Dts.Variables["$Project::prmLocalPath"].Value.ToString() + "etrainingusers_0.csv";

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

                TransferOperationResult transferResult = session.PutFiles(
                   localPath, remotePath, false, transferOptions);

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

                // Print results
                bool fireAgain = false;
                foreach (TransferEventArgs transfer in transferResult.Transfers)
                {
                    Dts.Events.FireInformation(0, null,
                        string.Format("Upload of {0} succeeded", transfer.FileName),
                        null, 0, ref fireAgain);
                }
            }

            Dts.TaskResult = (int)DTSExecResult.Success;
        }
        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;
        }
    }
}

正常运行的单个文件上传脚本 #

#region Namespaces
using System;
using System.Windows.Forms;
using System.IO;
using System.Reflection;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Tasks.ScriptTask;
using WinSCP;
#endregion

namespace ST_4977fa724bbb4816bfe3d3c8f2376a79
{
    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]

    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {

        private const string LoadingLog = @"C:\winscp\elearningupload.log";
        private const string AssemblyPath = @"C:\winscp\WinSCPnet.dll";

        static ScriptMain()
        {
            DebugLoading("Setting up assembly resolve handler");
            AppDomain.CurrentDomain.AssemblyResolve += AssemblyResolve;
        }

        private static void DebugLoading(string message)
        {
            message = DateTime.Now.ToLongTimeString() + ": " + message + Environment.NewLine;
            // Uncomment to debug assembly loading issues
            File.AppendAllText(LoadingLog, message);
        }

        private static Assembly AssemblyResolve(object sender, ResolveEventArgs args)
        {
            try
            {
                DebugLoading($"Resolving assembly {args.Name}");
                string name = new AssemblyName(args.Name).Name;
                DebugLoading($"Assembly name {name}");
                if (name.Equals("WinSCPnet", StringComparison.InvariantCultureIgnoreCase))
                {
                    DebugLoading($"Loading {name} from {AssemblyPath}");
                    Assembly assembly = Assembly.LoadFile(AssemblyPath);
                    DebugLoading("Loaded");
                    return assembly;
                }
                DebugLoading("Not WinSCPnet");
                return null;
            }
            catch (Exception e)
            {
                DebugLoading($"Exception: {e}");
                throw;
            }
        }
        public class AppException : Exception
        {
            public AppException(String message) : base(message)
            { }

            public AppException(String message, Exception inner) : base(message, inner)
            { }
        }

        public void Main()
        {
            SessionOptions sessionOptions = new SessionOptions
            {
                Protocol = Protocol.Sftp,
                HostName = (string)Dts.Variables["$Project::prmHost"].Value,
                UserName = (string)Dts.Variables["$Project::prmUser"].Value,
                Password = (string)Dts.Variables["$Project::prmPassword"].Value.ToString(),
                SshHostKeyFingerprint = (string)Dts.Variables["$Project::prmFingerprint"].Value,
                PortNumber = Convert.ToInt32(Dts.Variables["$Project::prmPort"].Value)
            };

            try
            {
                using (Session session = new Session())
                {
                    session.ExecutablePath = @"C:\winscp\winscp.exe";
                    session.SessionLogPath = Dts.Variables["$Project::prmLogFilePath"].Value.ToString();
                    //session.SessionLogPath = @"C:\winscp\elearningupload.log";

                    //Directories
                    string fname = Dts.Variables["$Project::prmFilename"].Value.ToString();
                    string locpath = Dts.Variables["$Project::prmLocalPath"].Value.ToString(); //This is the local directory
                    string path = Dts.Variables["$Project::prmPath"].Value.ToString(); //This is the desired remote directory
                    string fullname = locpath + fname;

                    // Connect
                    session.Open(sessionOptions);

                    // Upload files
                    TransferOptions transferOptions = new TransferOptions();
                    transferOptions.TransferMode = TransferMode.Binary;
                    transferOptions.PreserveTimestamp = false;
                    var permissions = new FilePermissions { Octal = "0750" };
                    transferOptions.FilePermissions = permissions;
                    TransferOperationResult transferResult = session.PutFiles(@fullname, @path, false, transferOptions);

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

                    // Print results
                    bool fireAgain = false;
                    foreach (TransferEventArgs transfer in transferResult.Transfers)
                    {
                        Dts.Events.FireInformation(0, null,
                            string.Format("Upload of {0} succeeded", transfer.FileName),
                            null, 0, ref fireAgain);
                    }
                }
                Dts.TaskResult = (int)ScriptResults.Success;
            }

            catch (AppException e)
            {
                //Raise Error event
                Dts.Events.FireError(0, "Script Task", "Error", string.Empty, 0);
                if (e.InnerException != null)
                    Console.WriteLine("Inner exception: {0}", e.InnerException);

                Dts.Events.FireError(0, null,
                string.Format("Error when using WinSCP to upload files: {0}", e),
                null, 0);

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


        }

        #region ScriptResults declaration
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion
    }
}

报错排查建议 #

• 补全WinSCP依赖配置：对比正常运行的脚本，报错脚本未设置session.ExecutablePath，也没有实现AssemblyResolve事件处理。SSIS环境下WinSCP.NET组件加载存在特殊性，必须确保WinSCP.exe与WinSCPnet.dll路径正确，要么通过AssemblyResolve手动加载组件，要么显式指定ExecutablePath指向WinSCP.exe的位置。
• 启用组件加载日志：参考正常脚本中的DebugLoading方法，取消注释日志写入代码，排查是否是组件加载失败导致的反射调用异常（“Invocation”类异常大多和组件加载、初始化失败相关）。
• 捕获完整异常信息：修改报错脚本的catch块，将e改为e.ToString()输出，这样能获取包含内部异常的完整堆栈信息，准确定位错误根源。
• 验证SSIS变量配置：确认所有项目变量（prmHost、prmUser、prmPassword等）已正确赋值，且在脚本任务的ReadOnlyVariables列表中已勾选，避免空值或类型转换错误。
• 检查日志路径权限：确保prmLogFileName对应的目录有写入权限，WinSCP无法写入日志时可能导致静默失败。

批量上传CSV文件的实现方案 #

基于正常运行的单个文件上传脚本，修改Main方法实现批量遍历上传：

public void Main()
{
    SessionOptions sessionOptions = new SessionOptions
    {
        Protocol = Protocol.Sftp,
        HostName = (string)Dts.Variables["$Project::prmHost"].Value,
        UserName = (string)Dts.Variables["$Project::prmUser"].Value,
        Password = (string)Dts.Variables["$Project::prmPassword"].Value.ToString(),
        SshHostKeyFingerprint = (string)Dts.Variables["$Project::prmFingerprint"].Value,
        PortNumber = Convert.ToInt32(Dts.Variables["$Project::prmPort"].Value)
    };

    try
    {
        using (Session session = new Session())
        {
            session.ExecutablePath = @"C:\winscp\winscp.exe";
            session.SessionLogPath = Dts.Variables["$Project::prmLogFilePath"].Value.ToString();

            string localDir = Dts.Variables["$Project::prmLocalPath"].Value.ToString(); // 本地CSV目录
            string remoteDir = Dts.Variables["$Project::prmPath"].Value.ToString(); // SFTP目标目录

            // 获取目录下所有.csv文件
            string[] csvFiles = Directory.GetFiles(localDir, "*.csv");
            if (csvFiles.Length == 0)
            {
                bool fireAgain = false;
                Dts.Events.FireInformation(0, null, "本地目录下未找到CSV文件", null, 0, ref fireAgain);
                Dts.TaskResult = (int)ScriptResults.Success;
                return;
            }

            // 连接SFTP服务器
            session.Open(sessionOptions);

            // 配置传输选项
            TransferOptions transferOptions = new TransferOptions();
            transferOptions.TransferMode = TransferMode.Binary;
            transferOptions.PreserveTimestamp = false;
            var permissions = new FilePermissions { Octal = "0750" };
            transferOptions.FilePermissions = permissions;

            bool fireAgainInfo = false;
            // 循环上传每个CSV文件
            foreach (string localFile in csvFiles)
            {
                TransferOperationResult transferResult = session.PutFiles(localFile, remoteDir, false, transferOptions);
                transferResult.Check(); // 抛出上传错误

                foreach (TransferEventArgs transfer in transferResult.Transfers)
                {
                    Dts.Events.FireInformation(0, null,
                        string.Format("上传成功: {0}", transfer.FileName),
                        null, 0, ref fireAgainInfo);
                }
            }
        }
        Dts.TaskResult = (int)ScriptResults.Success;
    }
    catch (Exception e) // 改为捕获所有Exception，避免遗漏错误
    {
        Dts.Events.FireError(0, null,
            string.Format("SFTP上传错误: {0}", e.ToString()),
            null, 0);

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

批量代码说明 #

1. 使用Directory.GetFiles(localDir, "*.csv")遍历指定目录下所有CSV文件；
2. 增加空文件判断，避免无文件时执行无效操作；
3. 循环调用session.PutFiles实现批量上传，保留原有的传输配置和错误检查；
4. 统一捕获所有Exception，确保任何错误都能输出完整信息。

内容的提问来源于stack exchange，提问作者camromere