学习SSIS脚本任务与脚本组件

本文介绍了SSIS脚本任务和脚本组件，这是SQL Server集成服务（SSIS）中的脚本工具。

简介

SQL Server集成服务是一套用于数据集成操作的工具。它被用来促进数据仓库过程和解决复杂的业务问题。集成服务包含一些功能（任务和组件），允许开发人员从各种来源（如XML数据文件、平面文件和关系数据源）提取和转换数据，然后将数据加载到一个或多个目的地。SSIS的任务和组件几乎支持数据集成操作中所需要的大部分知名操作，例如：。

1. 从流行的数据源读取数据：文本文件、XML、CSV、关系数据库、Excel表格......
2. 将数据导出为不同的数据格式
3. 将数据插入到不同的数据存储库
4. 操作系统任务
5. 执行维护任务（备份、恢复、清理）
6. 数据仓库操作（数据质量任务，分析任务）
7. 多种多样的数据转换操作

除了所有这些功能，集成服务的主要优势之一是，它允许.NET开发人员实现任何复杂的逻辑，并利用来自强大的.NET框架或第三方的库。这种能力可以使用两个主要对象：SSIS脚本任务和SSIS脚本组件。

在下面的章节中，我们将简要地解释这两个对象，并说明每个对象的一些使用情况。

• 题外话：在SSIS中，任务可以在包控制流层面执行，而组件可以在数据流中执行。

SSIS任务分为以下几种：数据流任务、数据准备任务、工作流任务、SQL Server任务、分析服务任务、维护任务、自定义任务和脚本任务。

脚本任务*（主要是SSIS脚本任务）*通过使用脚本扩展了包的功能。它所执行的功能是标准的集成服务任务所不能提供的。

图1 - SSIS工具箱中的脚本任务描述

SSIS脚本任务是一个毫不费力的任务，不需要太多的配置。初始化脚本任务时，用户必须指定要使用的编程语言、只读和读写的变量。

图2 - 脚本任务编辑器

在SSIS脚本任务中支持两种编程语言。Visual Basic .NET和C#。

图3 - 选择脚本编程语言

如图2所述，我们应该点击 "编辑脚本 "按钮来打开脚本编辑器。脚本任务使用微软Visual Studio Tools for Applications（VSTA）作为脚本本身的开发环境。

题外话：Microsoft Visual Studio Tools for Applications可以让你在与VSTA集成的应用程序中添加和运行VSTA的定制功能

图4 - 脚本编辑器

如上图所示，在VSTA项目中，有一个名为ScriptMain.cs的类。这就是我们要实现脚本的类。在ScriptMain类中，我们应该在Main函数中编写我们的代码。

读取和写入变量

要在脚本中使用一个变量，我们应该确保在ReadOnlyVariables和ReadWriteVariables属性中选择这个变量，这取决于我们的代码是否需要写入这个变量。

要读取一个存储在SSIS变量中的整数值，我们应该使用以下代码。

int integerValue = (int)Dts.Variables["$Package::IntegerVariable"].Value;

此外，为了在SSIS变量中写入一个整数值，我们应该使用下面的代码。

Dts.Variables["User::IntegerVariable"].Value = 1;

如上面的例子所示，变量是从 "Dts "命名空间访问的，它允许脚本与外部包对象交互。

引发事件

一个SSIS脚本任务可以引发三种类型的事件用于记录。错误，警告，和信息。

要引发一个错误，我们应该使用FireError()方法。

Dts.Events.FireError(18, "Process Values", "Bad value", "", 0);

要引发一个警告，我们应该使用FireWarning()方法。

Dts.Events.FireWarning(14, "Process Values", "No values received for input", "", 0);

为了引发信息，我们应该使用FireInformation()方法。

bool fireAgain = false;
Dts.Events.FireInformation(3, "Process Values", "Processing has started", "", 0,  ref fireAgain);  

使用连接管理器

一些类型的连接管理器可以在SSIS脚本任务中使用，以便读取它们的一些配置。作为一个例子，为了从文件连接管理器中读取文件路径，我们可以使用以下代码。

object sourceFlatFileConnection = Dts.Connections["Source Flat File"].AcquireConnection(Dts.Transaction);
string filePath = (string)sourceFlatFileConnection;

SSIS脚本任务用例

有几个使用脚本任务的用例，如列出文件、操作Excel文件、检查空文件。

SSIS脚本组件

正如我们在介绍中提到的，任务并不打算在数据流任务中运行。它们只在控制流层面使用。为了扩展数据流任务的功能，我们应该使用SSIS脚本组件。

SSIS脚本组件的配置比SSIS脚本任务更复杂。作为数据流任务管道的一部分，必须为组件配置输入和输出列元数据。

图5 - 脚本组件列配置

SSIS脚本组件有三个支持的功能。

• 脚本作为一个源：我们可以使用脚本组件从SSIS不支持的来源生成数据行，或者如果我们需要在加载数据时实现复杂的逻辑。如果作为一个源，脚本组件支持多种输出
• 脚本作为一种转换：我们可以使用一个脚本组件来执行复杂的数据转换。如果作为一个转换，脚本组件支持一个输入和多个输出。
• 脚本作为一个目标：如果我们需要将数据插入SSIS中不支持的目标类型，我们可以使用脚本组件作为目标。如果作为一个目标，脚本组件支持一个输入。

读取和写入变量

与脚本任务不同，在脚本组件中处理变量不需要 "Dts "命名空间。变量将作为Variables对象的强类型的属性来使用。

string filePath = Variables.FilePath;

此外，对变量的写入只能在PostExecute()方法中进行，因为SSIS变量的值在脚本执行期间不能被修改。

public override void PostExecute()
{
    base.PostExecute();
    Variables.FilePath = "";
}

另一种在脚本组件中访问变量的方法是使用VariableDispenser属性，通过调用Me.VariableDispenser进行访问。在这种情况下，我们没有使用变量的类型和命名的访问器属性，而是直接访问变量。

SSIS脚本组件使用案例

在[微软的官方文档]中，他们提到了使用SSIS脚本组件的四个目的。

1. 对数据应用多种转换，而不是在数据流中使用多种转换组件
2. 访问现有.NET程序集中的业务规则
3. 使用自定义公式和函数
4. 验证列数据并跳过包含无效数据的记录

总结

在发表了一篇关于SSIS脚本组件的详细文章后，本文重点介绍了SSIS脚本任务和它在我们的集成服务包中的使用。此外，我们简要地提到了脚本组件。此外，我们还说明了这两种脚本功能的一些使用案例。

我们应该记住，脚本任务和脚本组件之间的主要区别是，每一个都可以在SSIS包的不同层次上执行，而且每一个都有其使用案例，即使它们的名字看起来很相似。