如何在SSIS包中从SFTP服务器下载CSV文件并按配置重命名

1. 配置包配置读取config文件参数 #

首先将所有可变参数存入XML格式的dtsConfig配置文件，需要包含的配置项如下：

• SFTP服务器地址
• SFTP服务端口（默认22）
• SFTP登录用户名
• SFTP登录密码（生产环境建议加密存储）
• SFTP远程CSV文件所在目录路径
• 需要下载的CSV源文件名
• 本地文件存储根目录路径

配置读取操作：
打开SSIS包，点击顶部菜单栏「SSIS」→「包配置」，勾选「启用包配置」后点击添加，配置类型选择「XML配置文件」，指定你的config文件路径，勾选所有需要读取的参数项完成配置，后续所有参数可直接通过SSIS变量调用，无需修改包代码。

2. 执行SFTP文件下载 #

根据你使用的SQL Server版本选择对应方案：

方案A：内置SFTP任务（SQL Server 2017及以上版本适用） #

• 从SSIS工具箱拖入「SFTP任务」，双击打开配置面板
• SFTP连接信息直接选择从config读取的对应变量
• 操作类型选择「接收文件」，远程文件路径填配置读取的远程目录+CSV源文件名，本地存储路径填配置读取的本地根路径
• 可选勾选「覆盖本地已存在文件」，避免重复下载时报错

方案B：脚本任务调用WinSCP（全SQL Server版本兼容） #

低版本SQL Server无内置SFTP任务时使用该方案，需提前下载WinSCP的.NET程序集到本地可访问路径：

• 拖入「脚本任务」，双击打开，在ReadOnlyVariables列表中添加所有从config读取的参数变量
• 点击「编辑脚本」，使用C#编写下载逻辑，核心代码示例：

using WinSCP;
using System.IO;

public void Main()
{
    // 读取配置变量
    string sftpHost = Dts.Variables["User::SFTP_Host"].Value.ToString();
    int sftpPort = int.Parse(Dts.Variables["User::SFTP_Port"].Value.ToString());
    string sftpUser = Dts.Variables["User::SFTP_UserName"].Value.ToString();
    string sftpPwd = Dts.Variables["User::SFTP_Password"].Value.ToString();
    string remoteDir = Dts.Variables["User::SFTP_RemoteDir"].Value.ToString();
    string sourceFileName = Dts.Variables["User::Source_CSV_Name"].Value.ToString();
    string localDir = Dts.Variables["User::Local_Storage_Dir"].Value.ToString();

    // 初始化SFTP会话
    SessionOptions sessionOpts = new SessionOptions
    {
        Protocol = Protocol.Sftp,
        HostName = sftpHost,
        PortNumber = sftpPort,
        UserName = sftpUser,
        Password = sftpPwd,
        // 生产环境建议删除该行，替换为实际的SSH主机密钥指纹，提升安全性
        GiveUpSecurityAndAcceptAnySshHostKey = true
    };

    using (Session sftpSession = new Session())
    {
        sftpSession.Open(sessionOpts);
        // 执行文件下载
        TransferOptions transferOpts = new TransferOptions { TransferMode = TransferMode.Binary };
        TransferOperationResult result = sftpSession.GetFiles(
            RemotePath.Combine(remoteDir, sourceFileName),
            Path.Combine(localDir, sourceFileName),
            false, transferOpts
        );
        result.Check();
    }
    Dts.TaskResult = (int)ScriptResults.Success;
}

3. 文件重命名追加时间戳 #

• 新建SSIS字符串类型变量TimeStamp，设置表达式为：
  REPLACE(REPLACE(REPLACE((DT_WSTR,30)GETDATE(),"-",""),":","")," ","")
  该表达式会生成格式为yyyyMMddHHmmss的纯数字时间戳，避免文件名出现特殊字符报错。
• 再新建字符串类型变量New_CSV_Name，设置表达式为：
  LEFT(@[User::Source_CSV_Name], LEN(@[User::Source_CSV_Name]) - 4) + "_" + @[User::TimeStamp] + ".csv"
• 拖入「文件系统任务」，操作类型选择「重命名文件」，源文件指向刚下载到本地的CSV文件，目标文件名使用New_CSV_Name变量即可。

可选优化点 #

• 下载前增加SFTP远程文件存在性校验，避免下载不存在的文件导致包运行失败
• 下载完成后增加文件大小校验，确认文件完整后再执行重命名操作
• 敏感参数如SFTP密码不要明文存储在config文件中，可使用SSIS包保护级别加密或者数据库加密存储

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