• 欢迎访问金笔头博客,这是一个菜鸟(伪)程序员的自留地,欢迎访问我的github:点击进入

SSIS使用脚本任务(C#)操作SFTP文件

SSIS eason 4053次浏览 0个评论 扫描二维码

前言:

    之前写过一篇使用“执行进程任务”操作SFTP文件的短文,后来在做项目的过程中发现这个方法还是不够完美,之所以这么说是因为我们发现使用“执行进程任务”去SFTP下载文件的时候,无法直接获取WinSCP的执行的状态信息,虽然“执行进程任务”有个参数StandOutputVariable,但是配好之后发现还是获取不到输出信息,本文将结合实际项目去描述如何解决这个问题。

场景:

    我们有几个文件需要从SFTP上取,但是这几个文件是客户不定期上传的,文件命名格式为FileName_YYYYMMDD.xlsx,我的想法是建一个job,每天定时去SFTP上下载,如果有就下载下来并将xlsx中的数据导入数据库,如果没有当天的文件也不会报错,只是记录日志,后续加载数据的操作不执行。那么问题来了,如何去判断这个文件在SFTP上是否存在呢?之前的做法是不管有没有文件,先执行下载操作,然后执行脚本任务验证本地对应路径下有没有该文件,如果有就继续后面的加载操作,没有就只写日志。现在我想省掉本地文件验证的过程,直接在第一步下载的时候就获取文件是否存在的信息。

示例:

step1:新建变量
SSIS使用脚本任务(C#)操作SFTP文件
变量解释:
ScheduleDate<job的调度日期,默认是当天>
FileName<动态文件名,表达式“CSR_************_”+ REPLACE((DT_WSTR, 10) (DT_DBDATE) DATEADD(“d”, 0 , (DT_DBDATE) @[User::ScheduleDate] ), “-“, “”) +”.xlsx”>
LocalPath_IPOS_LR<本地文件存放路径>
RemoteFileLocation<远程文件的次级目录>
RemotePath_IN<远程文件的顶级目录>
Result<返回文件是否存在的标识,1为存在,0为不存在>
SFTP_Manual_string<远程SFTP的连接信息,包含用户名、密码、主机ip和端口>
WinSCP_Path<WinSCP.com的路径>
ErrorInfo<保存错误信息>
step2:设定脚本任务参数
SSIS使用脚本任务(C#)操作SFTP文件
编辑脚本任务,除了Result和ErrorInfo为可读写变量,其他均为只读变量。
step3:编辑脚本任务
public void Main()
{
            // 创建一个进程对象来执行WinSCP
            Process winscp = new Process();
            // 设置WinSCP可执行文件路径
            winscp.StartInfo.FileName = Dts.Variables["User::WinSCP_Path"].Value.ToString();
            // 设置WinSCP的执行参数 (无需更改)
            winscp.StartInfo.UseShellExecute = false;
            winscp.StartInfo.RedirectStandardInput = true;
            winscp.StartInfo.RedirectStandardOutput = true;
            winscp.StartInfo.StandardOutputEncoding = System.Text.ASCIIEncoding.UTF8;
            winscp.StartInfo.CreateNoWindow = true;
            // 设置会话参数
           string sessionOptionString = "option batch abort" + System.Environment.NewLine + "option confirm off";
            // 构建连接字符串
           string connectString = "open "+Dts.Variables["User::SFTP_Manual_string"].Value.ToString();
            //MessageBox.Show(connectString);
            // 构建命令字符串
           string getString = "get " + Dts.Variables["User::RemotePath_IN"].Value.ToString() 
                + Dts.Variables["User::RemoteFileLocation"].Value.ToString()
                + Dts.Variables["User::FileName"].Value.ToString()
                + " " + Dts.Variables["User::LocalPath_IPOS_LR"].Value.ToString();
            //MessageBox.Show(getString);
           // 新建输出变量来捕获执行状态信息
           string outStr = "", errStr = "";
            int returnVal = 1;

            // 使用try/catch 块可以捕获致命性的错误 (例如WinSCP.com路径指定错误).
           try
            {
                winscp.Start();
                winscp.StandardInput.WriteLine(sessionOptionString);
                winscp.StandardInput.WriteLine(connectString);
                winscp.StandardInput.WriteLine(getString);
                winscp.StandardInput.Close();
                winscp.WaitForExit();
                // 将WinSCP的执行状态信息赋值给变量outStr
                outStr = winscp.StandardOutput.ReadToEnd();
                returnVal = winscp.ExitCode;
            }
            catch (Exception ex)
            {
                errStr = "An error occurred when attempting to execute winscp.com: " + ex.Message;
            }

            if (errStr != "")
                Dts.Variables["User::ExecInfo"].Value = errStr;
            else
                Dts.Variables["User::ExecInfo"].Value = outStr;

            if (errStr != "" | outStr.Contains("错误码"))
                Dts.Variables["User::Result"].Value = "0";
            else
                Dts.Variables["User::Result"].Value = "1";

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



step4:调试包,下两个断点监控变量在脚本任务执行前和执行后的变化,并验证结果。
SSIS使用脚本任务(C#)操作SFTP文件
如下图,变量Result在执行前的值为x,ExecInfo的值为“”
SSIS使用脚本任务(C#)操作SFTP文件
脚本任务执行后,ExecInfo的值变成了winscp> option batch abort\r\nbatch           abort     \r\nreconnecttime   120       \r\nwinscp> option confirm off\r\nconfirm         off       \r\nwinscp> open sftp://*****:*****@111.111.123.10:22 \r\n寻找主机…\r\n连接到主机…\r\n正在验证…\r\n使用用户名 \”*****\”。\r\n以预置密码进行验证。\r\n已验证。\r\n正在开始会话…\r\n会话已开始。\r\n活动的会话:[1] *****@111.111.123.10\r\nwinscp> get /XXXX/XXXX/XXXXXXXXXXXXXXXXX/CSR_Manual_IPOS_LR_20160527.xlsx D:\\Test\\CSR_Interface_ETLS\\cdgFile\\IN\\Manual_files\\CSR_Manual_IPOS_LR\\\r\nCSR_Manual_IPOS_LR_20160527.xlsx |         376 KB |  467.4 KB/s | binary | 100%\r\nwinscp> \r\n
Result的值变为1,获取到文件是否存在的信息之后,我们就可以通过“优先约束”写表达式去控制下面的加载操作是否执行。
SSIS使用脚本任务(C#)操作SFTP文件
至此,我们就已经将下载文件判断后续步骤是否执行合成一步了。

金笔头博客, 版权所有丨如未注明 , 均为原创, 转载请注明SSIS使用脚本任务(C#)操作SFTP文件
喜欢 (1)
发表我的评论
取消评论

表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址