数据转换：SSIS数据流任务的实用技巧

### 数据转换：SSIS 数据流任务的实用技巧 数据转换是 ETL（Extract, Transform, Load）过程中至关重要的一环，它能让数据按照我们的需求进行定制。本文将介绍几种实用的数据转换方法，包括派生列、审计转换、聚合转换和条件拆分。 #### 派生列：添加计算列 在 ETL 中，创建自定义列是常见需求。派生列转换允许我们编写自定义表达式来创建新列或替换现有列。 **准备工作**： - 在 `C:\SSIS\Ch03_Data Flow Task-Part 2-Transformations\Files` 目录下创建一个名为 `R01_Destination.csv` 的空文件，并以 UTF - 8 编码保存。 - 将默认创建的包重命名为 `P01_DerivedColumn.dtsx`。 **操作步骤**： 1. 创建一个新的集成服务类型项目，命名为 `R01_Derived Column`。 2. 将数据流任务拖放到控制流中，然后切换到数据流选项卡。 3. 将 OLE DB 源拖放到数据流选项卡中，打开 OLE DB 源编辑器。 4. 创建与 `AdventureWorks2012` 数据库的新连接，将数据访问模式设置为“表或视图”，然后从表或视图列表中选择 `[HumanResources].[vEmployeeDepartmentHistory]`。 5. 点击“预览”，可以看到有 `StartDate` 和 `EndDate` 列，但大多数行的 `EndDate` 值为 `Null`。 6. 在“列”选项卡中，仅选择 `FirstName`、`LastName`、`StartDate` 和 `EndDate`。 7. 从 SSIS 工具箱的“常用”部分，将派生列拖放到 OLE DB 源之后的数据流中，并将数据路径（绿色箭头）从 OLE DB 源连接到派生列。 8. 双击派生列转换，打开派生列转换编辑器。 9. 在“表达式”字段中写入以下表达式： ```plaintext YEAR(ISNULL(EndDate) ? GETDATE() : EndDate) - YEAR(StartDate) ``` 10. 在“派生列名称”字段中输入 `YearsInCompany`。 11. 将平面文件目标拖放到派生列之后，并将数据路径从派生列连接到平面文件目标。 12. 在平面文件目标编辑器中，点击“新建”，将平面文件格式设置为“分隔符”。 13. 浏览 `C:\SSIS\Ch03_Data Flow Task-Part 2-Transformations\R01_DerivedColumn\Files` 目录下的 `R01_Destination.csv` 文件。 14. 检查第一行数据中的列名，然后转到“列”选项卡，确认所有必需的列都存在，你会看到 `YearsInCompany` 列以及其他列。 15. 关闭平面文件连接管理器和平面文件目标，运行包。 16. 打开 `C:\SSIS\Ch03_Data Flow Task-Part 2-Transformations\Files` 目录下的 `R01_Destination.csv` 文件，你会在其他列之后看到带有计算值的新 `YearsInCompany` 列。 **表达式的构成**： 表达式类似于 C# 的函数表达式，可用的函数分为四类： | 函数类别 | 说明 | | ---- | ---- | | 数学函数 | 用于数学计算 | | 字符串函数 | 处理字符串相关操作 | | 日期/时间函数 | 处理日期和时间数据 | | NULL 函数 | 处理空值情况 | 表达式还可以包含列、变量、参数、运算符和类型转换，不一定所有部分都要同时出现。 #### 审计转换：在数据流中记录日志 在 SSDT 环境中运行 SSIS 包时，我们可以实时查看错误、正在执行的任务和组件以及管道内移动的数据。但在实际生产中，SSIS 包通常以批处理模式运行，如果不启用和使用日志，很难了解具体情况。 **准备工作**： - 打开 SQL Server 数据工具（SSDT），创建一个新的 SSIS 项目。 - 为 SSIS 项目提供名称和位置，然后继续。 - 将默认创建的包重命名为 `P01_Audit.dtsx`。 **操作步骤**： 1. 在控制流选项卡中添加数据流并打开它。 2. 为源组件创建到 `AdventureWorksLT2012` 示例数据库的 OLEDB 连接。 3. 在连接管理器中右键单击，添加新的 OLEDB 连接。 4. 在数据连接列表中选择 `AdventureWorksLT2012` 数据库，如果未列出，点击“新建”创建一个。 5. 点击“确定”，将连接重命名为 `cmAdventureWorksLT`。 6. 从 SSIS 工具箱的“其他源”部分，将 OLE DB 源组件拖放到数据流设计区域。 7. 打开组件进行编辑。 8. 上一步创建的连接将自动分配给源组件，如果没有，从相应的下拉菜单中选择。 9. 在“数据访问模式”属性中选择“表或视图”。 10. 在“表或视图名称”属性中，选择 `[SalesLT].[SalesOrderDetail]`。 11. 从 SSIS 工具箱的“其他转换”部分，将审计组件拖放到数据流设计区域。 12. 通过第一个组件的红色输出将此组件链接到上一个组件。 13. 打开组件进行编辑。 14. 从下拉列表中选择每个可用的系统变量。 15. 选择每个变量后，最终属性应如下所示。 16. 点击“确定”保存更改并关闭编辑器。 17. 从 SSIS 工具箱的“常用”部分，将联合全部组件拖放到数据流设计区域。 18. 通过第一个组件的红色输出将此组件链接到上一个组件。 19. 在将审计组件连接到联合全部组件的绿色行下方，右键单击并选择“启用数据查看器”选项，以查看管道中移动的数据。 20. 按 F5 执行包，检查添加到管道中的额外列。 **审计组件添加的系统变量**： | 变量名称 | 说明 | | ---- | ---- | | ExecutionInstanceGUID | 标识包执行实例的 GUID | | PackageID | 包的唯一标识符 | | PackageName | 包的名称 | | VersionID | 包的版本 | | ExecutionStartTime | 包开始运行的时间 | | MachineName | 计算机的名称 | | UserName | 启动包的用户的登录名 | | TaskName | 与审计转换关联的数据流任务的名称 | | TaskId | 数据流任务的唯一标识符 | 这些系统变量也可以通过派生列组件包含在 SSIS 管道中。 #### 聚合转换：聚合数据流 聚合转换允许我们按列对数据进行分组，并对