数据提取与加载：Recordset、Excel及CDC应用指南

### 数据提取与加载：Recordset、Excel 及 CDC 应用指南 在数据处理和集成的过程中，数据的提取、加载以及变更捕获是至关重要的环节。本文将详细介绍如何使用 Recordset Destination 组件将数据加载到内存，如何提取和加载 Excel 数据，以及如何利用 Change Data Capture (CDC) 功能检测数据库中的数据变更。 #### 1. 使用 Recordset Destination 加载数据到内存 Recordset Destination 组件虽然不常用，但能解决特定的实际问题。它不将数据保存到外部数据源，而是通过 SSIS 对象数据类型变量将数据保存到内存中的 ADO 记录集。在包执行期间，数据会临时存储在内存中，并在数据流中进行准备。通常，该组件会与控制流中的 Foreach 循环任务结合使用，以遍历每条记录并执行相应操作。 ##### 1.1 准备工作 - 打开 SQL Server Data Tools (SSDT) 并创建一个新的 SSIS 项目。 - 为 SSIS 项目提供名称和位置，然后继续。 - 选择默认创建的包并将其重命名为 `P01_RecordsetDestination.dtsx`。 ##### 1.2 操作步骤 1. 向控制流中添加一个数据流并打开它。 2. 创建一个指向示例文件 `R05_CustomerData.txt` 的平面文件连接： - 右键单击连接管理器，选择“新建平面文件连接…”。 - 点击“浏览...”按钮，找到示例平面文件 `R05_CustomerData.txt`。 - SSIS 会自动检测文件的属性，如编码和分隔符（本示例中列分隔符为制表符）。 3. 将“平面文件源”组件从 SSIS 工具箱的“其他源”拖放到数据流设计区域。 4. 打开该组件进行编辑，之前创建的连接将自动分配给源组件；若未分配，从相应下拉列表中选择。 5. 将“Recordset 目标”组件从 SSIS 工具箱的“其他目标”拖放到数据流设计区域。 6. 通过第一个组件的绿色输出将此组件与前一个组件链接起来。 7. 在 SSIS 变量面板中，创建一个新的对象类型的 SSIS 变量，用于存储平面文件源提供的数据。 8. 打开“Recordset 目标”组件进行编辑。 9. 在“自定义属性”组下的“VariableName”中，选择之前创建的 SSIS 变量。 10. 选择管道中需要包含在内存中的列，勾选每个列对应的复选框。 11. 按 F5 执行包。 ##### 1.3 工作原理 上述步骤展示了如何从平面文件读取客户数据并将其保存到内存。在将数据保存到内存之前，可以进行一些转换操作，如排序、过滤、应用业务规则等。例如，如果客户欠款少于 1000 欧元，通过电子邮件联系客户；如果欠款超过 1000 欧元，则通过短信联系以确保客户收到提醒。此外，该组件还常用于需要从文件系统读取多个文件的场景，将文件列表保存到内存并结合 Foreach 循环逐个读取文件。 #### 2. 提取和加载 Excel 数据 由于安全原因，系统倾向于使用平面文件，但人们更喜欢在 Excel 中工作。因此，Excel 文件在各组织中广泛存在，其中包含的重要数据需要进行集成和分析。在 SSIS 项目中，经常会使用 Excel 源和目标组件来处理 Excel 数据。 ##### 2.1 准备工作 - 打开 SQL Server Data Tools (SSDT) 并创建一个新的 SSIS 项目。 - 为 SSIS 项目提供名称和位置，然后继续。 - 选择默认创建的包并将其重命名为 `P01_WorkWithExcel.dtsx`。 ##### 2.2 操作步骤 1. 向控制流中添加一个数据流并打开它。 2. 创建一个指向示例文件 `R06_CustomerData.xlsx` 的 Excel 文件连接： - 右键单击连接管理器，点击“新建连接…”。 - 在连接管理器类型列表中，选择 Excel 类型并点击“添加...”按钮。 - 点击“浏览...”按钮，找到示例 Excel 文件 `R06_CustomerData.xlsx`。 3. 将“Excel 源”组件从 SSIS 工具箱的“其他源”拖放到数据流设计区域。 4. 打开该组件进行编辑，之前创建的连接将自动分配给源组件；若未分配，从相应下拉列表中选择。 5. 在“数据访问模式”属性中，选择“表或视图”。 6. 在“Excel 工作表名称”中，选择第一个工作表 `Sheet1$`。 7. “错误输出”选项卡可在转换出错时将行重定向到目标。 8. 将“联合全部”组件从 SSIS 工具箱的“通用”拖放到数据流设计区域。 9. 通过第一个组件的红色输出将此组件与前一个组件链接起来。 10. 将“Excel 目标”组件从 SSIS 工具箱的“其他目标”拖放到数据流设计区域。 11. 通过第一个组件的绿色输出将此组件与前一个组件链接起来。 12. 打开“Excel 目标”组件进行编辑，转到“映射”选项卡，确保管道中的输入列映射到目标列。 13. 点击“确定”按钮完成此组件的配置。 14. 按 F5 执行包。 ##### 2.3 工作原理 上述步骤展示了如何从 Excel 文件的一个工作表读取客户数据并将其插入到同一 Excel 文件的另一个工作表中。在使用 Excel 进行 SSIS 包操作时，需要考虑以下关键要点： - **数据访问模式**：Excel 源提供四种不同的数据访问模式： - 表或视图 - 变量中指定的表或视图 - SQL 命令的结果（查询可以是参数化查询） - 存储在变量中的 SQL 命令的结果 例如，可以使用以下 SQL 语句从 Excel 工作表中提取数据： ```sql SELECT ID, Customer_Name, Customer_Email, Money_Owed FROM [Sheet1$] ``` - **数据类型**：Excel 识别的数据类型有限，这通常会在处理 Excel 数据时产生大量问题。例如，源中的数字列在 Excel 中会被视为双精度浮点数 (DT_R8)，字符串列会被识别为长度为 255 的 Unicode 字符串 (nvarchar)。为克服这一限制，建议将某些列的外部列（源中的列）设置为长字符串，并在输出列中设置为正确的