SSIS脚本组件的使用指南

### SSIS脚本组件的使用指南 #### 1. 变量操作与脚本任务基础 在SSIS中，使用变量时需要注意一些规则。在VB.NET里，访问变量的方式如下： ```vbnet Dts.Variables("User::VariableName").Value ``` 要注意，变量名应包含命名空间部分（如User），并且变量名是区分大小写的。若要向变量中写入数据，需要将其选为可读写变量（ReadWriteVariables）。 脚本任务的入口点是`Main()`方法，该方法会被执行。例如，在创建邮件消息的脚本中，使用了`System.Net.Mail`命名空间下的`MailMessage`类来创建带有发件人和收件人地址的邮件消息。后续三行脚本用于生成带有特殊格式的邮件主题和HTML正文。对于发送SMTP邮件，则使用了同一命名空间下的`SmtpClient`，通过两行代码配置SMTP主机地址和登录凭据，最后调用`SmtpClient`对象的`Send`方法发送邮件。 脚本任务的结果对于优先级约束很重要，至少需要有一个`ScriptResult`。若要将脚本任务的结果设为失败，可以使用如下代码： ```csharp Dts.TaskResult = (int)ScriptResults.Failure; ``` 另外，通过`Dts.Events.FireError`可以从脚本任务触发特殊事件，更多关于在脚本任务中引发事件的信息可参考：[http://msdn.microsoft.com/en-us/library/ms136054.aspx](http://msdn.microsoft.com/en-us/library/ms136054.aspx)。 #### 2. 脚本组件作为转换组件 在SSIS的数据流中编写脚本是脚本编写的一个重要方面，脚本组件是SSIS数据流中最强大的组件之一，可作为源、转换和目标组件使用。下面以创建行号为例，介绍如何使用转换脚本组件。 ##### 操作步骤： 1. 创建一个SSIS项目，命名为`R02_ScriptComponent as Transformation`。 2. 从SSIS工具箱添加一个数据流任务，然后切换到数据流选项卡。 3. 添加一个OLE DB源，创建与`AdventureWorks2012`数据库的连接，将数据访问模式设置为表或视图，并选择`HumanResources.Department`作为源表。 4. 从OLE DB源连接一条数据路径到脚本组件，然后打开脚本转换编辑器。 5. 将脚本语言设置为Visual C# 2010。 6. 转到“输入和输出”选项卡，展开“输出0”，在“输出列”下添加一个数据类型为`DT_I4`的列，命名为`RowNumber`。 7. 回到“脚本”选项卡，点击“编辑脚本”。 8. 在`ScriptMain`类的主体中，编写以下代码： ```csharp public class ScriptMain : UserComponent { int RowNumber = 0; } ``` 9. 在`Input0_ProcessInputRow`方法中，编写以下代码： ```csharp public override void Input0_ProcessInputRow(Input0Buffer Row) { RowNumber++; Row.RowNumber = RowNumber; } ``` 10. 保存并关闭编辑器。 11. 向数据流中添加一个记录集目标，将结果写入一个对象变量。同时，在脚本组件和记录集目标之间的数据路径上启用数据查看器。 12. 运行包，验证输出数据流中是否有新的`RowNumber`列。 ##### 工作原理： 脚本组件是数据流中唯一能扮演源、转换和目标三种角色的组件。在这个例子中，转换脚本组件是作为同步转换使用的。当脚本组件被设置为转换时，它没有默认的输入和输出列。选择输入列时，可在脚本组件编辑器的“输入列”选项卡中选择需要在脚本中访问的列。对于输出列，则需要在“输入和输出”选项卡中直接添加。该选项卡中有一个“添加输出”按钮，可根据需要为转换添加多个输出。 脚本组件的脚本类与脚本任务完全不同。脚本任务只有一个包含执行代码的`Main`方法，而脚本组件有多个方法，每个方法在组件行为中都有特定作用。在这个简单的转换脚本组件例子中，涉及到以下三个事件和两个属性： - **PreExecute**：该方法在脚本组件执行前执行，可用于建立与资源的连接和打开流，每个脚本组件只执行一次。 - **PostExecute**：该方法在脚本组件执行后运行，主要用于释放`PreExecute`方法中获取的资源，并关闭连接和流，每个脚本组件也只执行一次。 - **InputName_ProcessInputRow**：该方法会根据流入脚本组件的数据流中的行数执行，即数据流中的每一行都会触发此方法。`Row`参数的类型为`InputNameBuffer`，包含当前行的所有数据。 - **InputNameBuffer**：`ProcessInputRow`方法中的`Row`参数包含数据流中对应记录的数据，可通过`Row.ColumnName`方法修改或读取每行的值。 - **ColumnName, ColumnName_IsNull**：对于脚本组件中的每个输入或输出列，`Row`对象都会有两个属性，`ColumnName`属性包含当前行该列的实际值，`ColumnName_IsNull`属性用于判断该列是否为`Null`值。 #### 3. 脚本组件作为源组件 在某些情况下，数据流中默认的内置源无法打开特殊的数据源，比如一些非常老旧或很少使用的数据源，或者是特殊格式的源结构。这时，脚本组件作为源组件就能帮助我们编写自定义代码，以所需的方式从任何源获取数据。 ##### 示例背景： 这里有一个具有特殊格式的平面文件（该文件是下一个脚本组件作为目标组件示例的输出），内置的平面文件源无法按要求打开它。我们将使用脚本组件作为源组件，并创建相应的输出。该文件包含一些页眉和页脚行，应忽略，还有一些数据行，分为三行。第一行只有一个员工的`NationalIDNumber`，第二行和第三行分别列出该员工的个人信息和专业信息。我们需要读取这个特殊文件的内容，并创建两个输出，一个用于员工的个人信息，另一个用于员工的专业信息。 ##### 操作步骤： 1. 创建一个新的SSIS项目，命名为`R03_ScriptComponent as Source`。 2. 添加一个新的数据流任务，在数据流选项卡中添加两个对象类型的包变量，分别命名为`PersonalInf`和`ProfessionalInf`。 3. 添加一个字符串类型的变量，命名为`SourceFilePath`，并将其值设置为`C:\SSIS\Ch08_Scripting\R03_ScriptComponent As Source\Files\data.txt`。 4. 将一个脚本组件拖放到数据流中，在“选择脚本组件类型”对话框中选择“源”。 5. 在脚本组件编辑器的“脚本”选项卡的“只读变量”部分中，选择`SourceFilePath`变量。 6. 转到“输入和输出”选项卡，注意作为源组件时没有输入。 7. 将默认的“输出0”名称改为`PersonalInformationOutput`。 8. 在该输出下添加以下输出列：`NationalIDNumber`、`FirstName`、`LastName`、`Gender`和`MaritalStatus`，将所有列的数据类型设置为`DT_WSTR`，并保留默认长度50。 9. 添加另一个输出，命名为`ProfessionalInformationOutput`。 10. 在该输出下添加数据类型为`DT_WSTR`的列：`NationalIDNumber`、`JobTitle`、`DepartmentName`和`DepartmentGroupName`，以及数据类型为`DT_DBDate`的列：`HireDate`、`StartDate`和`EndDate`。 11. 检查“输入和输出”配置是否与预期一致。 12. 回到“脚本”选项卡，点击“编辑脚本”。 13. 在`ScriptMain`类的主体中添加以下变量声明： ```csharp StreamReader reader = null; int RowIndex = 0; ``` 14. 在`PreExecute`方法中，打开`StreamReader`类并实例化`reader`变量： ```csharp public override void PreExecute() { base.PreExecute(); reader = new StreamReader(this.Variables.SourceFilePath); } ``` 15. 在`CreateNewOutputRows`方法中，编写以下代码： ```csharp public override void CreateNewOutputRows() { while (!reader.EndOfStream) { RowIndex++; if (RowIndex > 6) { string data = reader.ReadLine(); if (data == "End Of Data") { reader.ReadToEnd(); break; ```