SSIS 小脚本 - 文件路径验证

之前项目中经常有文件的读取或者输出操作,其中最重要的就是在处理文件输入/输出之前验证文件的路径是否存在,如果不存在就输出错误.

/*
   Microsoft SQL Server Integration Services Script Task
   Write scripts using Microsoft Visual C# 2008.
   The ScriptMain is the entry point class of the script.
*/
 
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;
 
namespace ST_TEST.csproj
{
    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {
 
        #region VSTA generated code
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion
         
        public void Main()
        {
            string sFilePath;
            string sPackagename;
 
            // Get the package name from SSIS Variables
            sPackagename = Dts.Variables["System::PackageName"].Value.ToString();
            // Get the file path from SSIS Variables
            sFilePath = Dts.Variables["User::IncomingFile"].Value.ToString();
 
            try
            {
                // Check for existence of file
                if ( !File.Exists( sFilePath ) )
                {
                    Dts.Events.FireError( 0,
                                          sPackagename,
                                          "File at file path: " + sFilePath + " does not exist",
                                          "",
                                          0 );
                }
            }
            catch ( System.Exception e )
            {
                Dts.Events.FireError(0,
                                      sPackagename,
                                      "Exception occurred check for file at file path: " + sFilePath + " with error: " + e.Message.ToString(),
                                      "",
                                      0);
            }
        }
    }
}

上面的 User::IncomingFile 在传入Script Component之前通过变量表达式就已经将文件夹路径, 文件名路径拼写在一起形成一个完整的文件路径, 所以进来后直接去验证和处理.

有时如果在输出文件之时,文件的输入目录和文件夹地址并不是固定的,而是通过变量来维护的. 在文件输出之前需要检查下文件输出的的目录和文件夹是否存在, 是否能够构成一个有效的输出路径,因此需要这样来检查下.

string directory = Dts.Variables["User::OutgoingDirectory"].Value.ToString();
string folder = Dts.Variables["User::OutgoingFolder"].Value.ToString();
string folderPath = Path.Combine(directory,folder);
 
if (!Directory.Exists(folderPath))
{
   Dts.Events.FireError(0,sPackageName,"Cannot find this folder path "+folderPath+" ","",0);
   return;
}