数据处理与系统信息查询的实用任务指南

# 数据处理与系统信息查询的实用任务指南 ## 1. 数据探查分析 在进行数据探查时，可通过在左侧面板选择表和探查类型，在右侧面板选择每一列，对每列的详细结果进行深入分析。其基本原理是在 ETL 过程中，对要提取、转换和加载的源数据进行探查。具体操作时，需识别可能导致质量问题的表和列，为每个表和列选择适用的探查类型，最后执行包以生成 XML 格式的输出报告。此外，Kimball Group 创建了一个 SQL Server Reporting Services (SSRS) 项目，用于在 SQL Server 数据库下进行数据探查分析，可访问 [http://www.kimballgroup.com](http://www.kimballgroup.com) 查看该项目。 ## 2. 批量插入数据到数据库：Bulk Insert 任务 ### 2.1 适用场景与限制 Bulk Insert 适用于需要快速将大量数据插入目标数据库的场景。在 SSIS 中有多种处理方式，其差异主要体现在向数据库插入大量数据的速度上。使用此方法时，需考虑以下限制： - 数据必须从文本文件传输到 SQL Server 表或视图。 - 目标必须是 SQL Server 数据库中的表或视图。 - 只有 sysadmin 固定服务器角色的成员才能执行包含此任务的包。 ### 2.2 准备工作 - 确保 SQL Server Management Studio (SSMS) 中存在专门创建的数据库。 - 本操作需要使用 PacktPub 数据库示例中的 Customers 表来存储平面文件提供的每个值。具体准备步骤如下： 1. 打开 SQL Server Management Studio (SSMS)，执行文件 `C:\SSIS\Ch02_Control Flow Tasks\Files\R09_ScriptCreateTable.sql` 中的 SQL 语句。 2. 打开 SQL Server Business Intelligence Development Studio (BIDS)，创建一个新的 SSIS 项目。 3. 为 SSIS 项目提供名称和位置，然后继续。 4. 选择默认创建的包，将其重命名为 `P01_BulkLoad.dtsx`。 ### 2.3 操作步骤 假设 SSIS 需要定期将大量数据导入中央存储库，且此导入应基于操作系统生成的平面文件并尽可能快速完成。操作步骤如下： 1. 由于源连接必须是平面文件，因此在配置主要任务之前，需创建一个平面文件连接。 2. 在连接管理器面板上，右键单击并选择“新建平面文件连接”。 3. 浏览到平面文件 `R09_SampleTxtFile01.txt`，转到“列”选项卡，保持所有属性为默认值，然后按“确定”。 4. 在控制流中添加一个 Bulk Insert 任务，用于批量插入源数据。 5. 打开任务进行编辑。 6. 在任务编辑器的“连接”选项卡中，选择“文件”属性的下拉框，然后选择上一步创建的指向源的平面文件连接。 7. 在“目标表”下拉框中，选择将目标表设置为 PacktPub 数据库示例中的 Customers 表的值。 8. 若要忽略平面文件中对应于标题的第一行，将“第一行”属性从 1 更改为 2。 9. 按 F5 执行包。如果执行成功，`PacktPub_SSISbook` 数据库中应存在新数据。 ### 2.4 工作原理 Bulk Insert 提供了一种以批量模式将平面文件中的记录插入数据库的方法。每个批次的行数、起始行号和结束行号、列分隔符、行分隔符等选项均可配置。该任务使用 SQL Server 内置的 Bulk Insert 操作。在目标连接中，需连接到基础数据库和表以进行 Bulk Insert 操作。指向平面文件的源连接仅用于创建文件连接管理器，这与后续章节将讨论的平面文件连接管理器不同。 格式定义了源文件的结构，有两种提供格式的方式： - **指定（Specify）**：在“格式”下拉属性中选择“指定”时，可以精确指定行分隔符和列分隔符。行分隔符定义了源文件中行的分隔方式（默认值为 `{CR}{LF}`，即换行符，但也可以指定任何字符或字符组合作为分隔符）；列分隔符定义了列的分隔方式，常见的列分隔符有逗号 (,)、分号 (;) 或竖线 (|)，也可以定义其他字符集作为列分隔符。 - **使用文件（Use File）**：在“格式”下拉框中选择“使用文件”时，会出现另一个属性，可以插入格式文件的物理地址。格式文件是一种语法文件，在需要以 Bulk Insert 模式将数据加载到 SQL Server 时非常有用。有关格式文件的外观和创建方法的更多信息，请参阅 [http://msdn.microsoft.com/en-us/library/ms178129.aspx](http://msdn.microsoft.com/en-us/library/ms178129.aspx)。格式文件在需要以比简单的行或列分隔符更高级的方式定义平面文件中的数据结构时非常有用，使用格式文件可以定义数据流中的每一列、其数据类型和其他一些高级选项。 ## 3. 查询系统信息：WMI 数据读取器任务 ### 3.1 任务概述 WMI 即 Windows Management Instrumentation，此任务可轻松访问基于 Windows 的操作系统的管理对象信息，例如硬盘驱动器数量、CPU 使用统计信息、正在运行的进程列表等。我们使用一种名为 WQL 的语言从 WMI 中获取信息，WQL 是用于 WMI 的查询语言。由于 WMI 为系统和操作系统提供了广泛的管理信息访问，SSIS 为 WMI 提供了两个任务，这里我们介绍 WMI 数据读取器任务，用于读取正在运行的进程信息并将其数据加载到文件中。 ### 3.2 准备工作 在地址 `C:\SSIS\Ch02_Control Flow Tasks\R10_WMI Data Reader Task\Files\RunningProcesses.txt` 创建一个空的目标文件。 ### 3.3 操作步骤 1. 创建一个新的 SSIS 项目，命名为 `R10_WMI Data Reader Task`。 2. 从 SSIS 工具箱的“其他任务”部分将 WMI 数据读取器任务拖放到控制流中。 3. 双击 WMI 数据读取器任务，转到“WMI 选项”选项卡。 4. 单击“WMI 连接”，创建一个新的 WMI 连接。在 WMI 连接编辑器中，选中“使用 Windows 身份验证”选项，然后测试连接以确认。 5. 单击“确定”，返回“WMI 选项”选项卡，在 `WmiQuerySource` 中写入以下语句： ```sql select * from Win32_Process ``` 6. 将 `OverwriteDestination` 设置为“覆盖目标”。 7. 在“目标”属性中创建一个新的文件连接管理器，并将其指向地址 `C:\SSIS\Ch02_Control Flow Tasks\R10_WMI Data Reader Task\Files\RunningProcesses.txt` 的现有文件（此文件在准备阶段创建）。 8. 单击“确定”，关闭 WMI 数据读取器任务编辑器，然后执行包。运行包后，检查 `RunningProcesses.txt` 文件，验证其中是否存在以逗号分隔值列出的所有正在运行的进程。 ### 3.4 工作原理 WMI 数据读取器任务通过 WQL 语句读取管理对象信息。在本操作中，我们读取了正在运行的进程列表及其相关信息。在 SSIS 中使用 WMI 任务时，需要创建一个 WMI 连接管理器，它有两个重要属性： - **服务器名称（Server name）**：指 WQL 应在其下运行并从中读取数据的计算机名称，以双反斜杠 (`\\`) 开头，后跟计算机名称，默认值为 `\\localhost`，表示当前系统。 - **命名空间（Namespace）**：是 WMI 类的命名空间，查询将在此命名空间下执行。WMI 类按命名空间划分，例如读取硬盘驱动器信息使用 `\root\cimv2` 命名空间，而获取 IIS 的某些信息则 `\root\WebAdministration` 命名空间更有用。有关 WMI 类和命名空间的列表，请参阅 [http://msdn.microsoft.com/en-us/library/aa394554(v=VS.85).aspx](http://msdn.microsoft.com/en-us/library/aa394554(v=VS.85).aspx)。 WMI 连接编辑器中的另一个重要选项是身份验证信息，由于 WMI 需要管理权限才能执行，因此需要提供具有相应权限的管理帐户。如果提供的帐户没有适当的权限，在执行 WMI 任务时将出现错误。 设置好 WMI 连接管理器后，需要指定 WMI 的 WQL 查询。WQL 具有简单的语法用于查询 WMI 类，本操作中使用的查询为： ```sql Select * from Win32_Process ``` 有关 WQL 语法的更多信息，请参阅 [http://msdn.microsoft.com/en-us/library/aa394606(v=VS.85).aspx](http://msdn.microso