开篇介绍
在 ETL 项目中的SSIS 包中的参数是可配置的,为了更好的控制人为传入的参数的正确性,或者为了确保 SSIS 程序的健壮性,那么在包执行之初会对 SSIS 包参数进行验证。包参数通过验证,则说明参数是可靠的,ETL 逻辑可以继续执行。如果不能通过验证,那么就需要格外的进行逻辑处理或者包将主动报错,这样就不会后期数据处理上的问题。并且前期的验证,可以省去后期逻辑处理阶段的重复验证工作。
一般情况下,Script Task 经常可以用来处理这方面的验证操作,特别是文件类,输入输出文件的路径验证等等 Script Task 是再合适不过的了。包括对时间参数的验证,时间段范围参数的验证,甚至包括数据源链接的验证都可以在 Script Task 中去完成,今天在这里着重介绍一下与文件路径相关的验证。
文件路径参数验证
通常情况下在一个有文件输入和输出的 ETL 项目中,对于文件路径配置的设计一般包含以下几类变量 -
- 文件输入方面的
- PV_INPUT_DIRECTORY
- PV_INPUT_FOLDER
- PV_INPUT_EMPLOYEE_FILE_NAME
- 文件输出方面的文件归档方面的
- PV_OUTPUT_DIRECTORY
- PV_OUTPUT_FOLDER
- PV_OUTPUT_EMPLOYEE_FILE_NAME
- 文件归档方面的
- PV_ARCHIVE_DIRECTORY
- PV_ARCHIVE_FOLDER
- PV_ARCHIVE_EMPLOYEE_FILE_NAME
要注意到上图中 PV_INPUT_EMPLOYEE_FILE_PATH 是经过验证之后没有问题的 PV_INPUT_DIRECTORY + PV_INPUT_FOLDER + PV_INPUT_EMPLOYEE_FILE_NAME 的路径拼接字符串。它的值可以直接通过 Expression 来配置或者通过 Script Task 中的代码来设置,它的使用是非常非常灵活的。在这个例子中,我们通过 Script Task 验证,验证通过后直接在 Script Task 中赋值。
要验证的内容
一个 ETL 包可能有一个或者多个文件输入源,那么这些文件数据源应该有指定的总目录,子文件夹,然后应该提供具体的文件名称。
- 对于输入源的验证就应该包含这三方面的验证,可以直接对文件验证,也可以分的很细的对每一个对象进行验证。
- 对于输出源来说,首先输出的总目录和子文件夹是需要被验证的,再次要取决于实际的需求,输出文件是要求事先存在或是不存在而决定对输出文件的验证。
- 对于归档类的验证和输出源应该是差不多一样的。
为了演示验证的过程,我们就只验证 INPUT 文件的路径是否合法,通过这个过程可以理解对其它的参数验证是如何实现的。
新建一个 Script Task,除了 PV_INPUT_EMPLOYEE_FILE_PATH 是可读写的外,其它的 INPUT 类型的变量都是只读的,并且把系统变量 PackageName 也加入进来。
编辑 Script Task 中的代码,因为涉及到文件类的操作,因此需要引入 IO 命名空间。
#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;
#endregion
Main 方法中的代码如下 -
public void Main()
{
// TODO: Add your code here
//System::PackageName,User::PV_INPUT_DIRECTORY,User::PV_INPUT_EMPLOYEE_FILE_NAME,User::PV_INPUT_FOLDER
//User::PV_INPUT_EMPLOYEE_FILE_PATH
String Packagename = Dts.Variables["System::PackageName"].Value.ToString();
String InputDirectory = Dts.Variables["User::PV_INPUT_DIRECTORY"].Value.ToString();
String InputFolder = Dts.Variables["User::PV_INPUT_FOLDER"].Value.ToString();
String InputFileName = Dts.Variables["User::PV_INPUT_EMPLOYEE_FILE_NAME"].Value.ToString();
String SInputFolder = Path.Combine(InputDirectory,InputFolder);
String SInputFilePath = Path.Combine(SInputFolder,InputFileName);
try
{
// Check input directory
if (!Directory.Exists(InputDirectory))
{
Dts.Events.FireError(0,Packagename,"Input Directory: "+InputDirectory +" doesn\'t exist!","",0);
Dts.TaskResult = (int)ScriptResults.Failure;
return;
}
// Check input folder
if (!Directory.Exists(SInputFolder))
{
Dts.Events.FireError(0, Packagename, "Input Folder: " + SInputFolder + " doesn\'t exist!", "", 0);
Dts.TaskResult = (int)ScriptResults.Failure;
return;
}
// Check file path
if (!File.Exists(SInputFilePath))
{
Dts.Events.FireError(0, Packagename, "Input file path: " + SInputFilePath+ " doesn\'t exist!", "", 0);
Dts.TaskResult = (int)ScriptResults.Failure;
return;
}
Dts.Variables["User::PV_INPUT_EMPLOYEE_FILE_PATH"].Value = SInputFilePath;
}
catch (System.Exception e)
{
Dts.Events.FireError(0, Packagename, "Exception occured when checking the file directory or path with error: " + e.Message.ToString(), "", 0);
}
Dts.TaskResult = (int)ScriptResults.Success;
}
保存 Script Task 并新建一个控制流任务并加载文件数据到指定的测试表中。
选中 Connection Manager 中的文件链接管理器,我们要将 PV_INPUT_EMPLOYEE_FILE_PATH 变量的值赋值给这个链接管理器中的文件链接字符串。
属性 ConnectionString 的赋值为 PV_INPUT_EMPLOYEE_FILE_PATH 。这样文件链接的字符串将根据 PV_INPUT_EMPLOYEE_FILE_PATH 变量的变化而变化了,这样文件路径就是可配置的了。
测试整个包,看看成功执行的效果。
数据是入库的。
下面分别修改 INPUT DIRECTORY, FOLDER 以及 FILE NAME 变量,看看验证的效果。
INPUT_DRECTORYTEST 这个目录不存在。
执行发生失败。
错误的信息非常清晰明显的指出了路径不存在!
[022_SCRIPT_VALIDATION] Error: Input Directory: D:\\BIWORKSPACE_FILE\\TS_BIWORK_SSIS\\INPUT_DIRECTORYTEST doesn\'t exist!
同样的测试发生在 INPUT FOLDER 和 INPUT FILE NAME 上就是,依次修改这两个变量为错误的值。
实际上,在上面的代码中可以只保留对 FILE PATH 的验证,因为 FILE PATH 的组成也是由 DIRECTORY 和 FOLDER 来组成的。
// Check file path
if (!File.Exists(SInputFilePath))
{
Dts.Events.FireError(0, Packagename, "Input file path: " + SInputFilePath+ " doesn\'t exist!", "", 0);
Dts.TaskResult = (int)ScriptResults.Failure;
return;
}
比如故意将 INPUT DIRECTORY 设置错误,放弃对INPUT DIRECTORY 和 FOLDER 的验证,那么整个文件的路径还是找不到的,因此只需要保留对文件路径最终的验证就可以了。
[022_SCRIPT_VALIDATION] Error: Input file path: D:\\BIWORKSPACE_FILE\\TS_BIWORK_SSIS\\INPUT_DIRECTORY1\\022\\EMPLOYEES.txt doesn\'t exist!
但是,在一个比较完整的 ETL 项目中,我们的验证应该是逐层的。特别是当源文件有很多的时候,我们的验证还是应该从主目录,目录到文件逐步验证。这样的信息更加准确一些和细致,对于我们的错误监控更加有利一些!