使用 SSIS 执行数据合并和审计
合并数据表并进行验证审计和错误日志记录。
使用 SSIS 进行数据导入审计
本文详细介绍了如何使用 SSIS 对合并数据进行审计——检查重复项，并添加其他验证规则。这种方法比在 VB 或 C# 中编写验证代码效率高得多。我还推荐阅读 Santosh Pujari 的文章 《在 Web 服务器中部署执行 SSIS 包的 Web 应用程序》 和 《从 ASP.NET 执行 SSIS 包（DTSX）》。
另外，如果您觉得本文对您有帮助，请不要忘记投票/留言。
目录
引言
数据导入验证通常很麻烦——尤其是当您需要像我过去那样使用 C# 编写所有验证规则时。
这时就轮到 SSIS 了。
本项目将演示一个简单的数据导入过程，执行数据验证（包括重复项检查），输出唯一记录，并记录错误。
背景
在我曾效力的一家大型 SEO/SEM 公司，我过去的一个项目是设计一个 Yahoo! SSP（Search Submit Pro）/ Paid Inclusions 集成工具（使用 C# .NET 3.5、LINQ to XML 和 LINQ to SQL），该工具接收网站的 URL 列表，允许内容编辑围绕这些 URL 生成内容，并生成一个 XML 数据文件提交给 Yahoo!。开发过程中最耗时/最繁琐的部分是编写验证代码，包括对多个字段以及不同关键字的内容验证。回想这个项目，这些内容验证完全可以用 SSIS 来完成，并且会节省大量时间。
现在，我的一个项目需要进行类似但强度较低的验证，但仍然需要进行错误日志记录以供历史审计。
入门
要求
- 已安装 SQL Server 2005 或 2008 以及 Business Intelligence Studio。
第一步是创建一个测试数据集。为此，我们需要测试空字段和重复记录。在这种情况下，重复记录不仅包括相同的用户 ID，还包括用户 ID 和部门的组合。
下载文章文件后，运行 _TestDataset.sql_ 来生成数据表 tbl_Errors、tbl_Users 和 tbl_UsersMerge，以及 tbl_Users 中的测试数据集。
起初，我将其从 Excel 电子表格导入，这很容易在数据流或其他方法中完成。
现在测试数据已加载，我们唯一真正有问题的数据是第九行，其中部门字段包含空值。通过再次执行数据导入，我们可以测试重复项，一旦成功。
理解流程
我们将遵循以下流程：
- 创建到测试数据集的连接。
- 添加一个查找数据流转换，该转换将连接 UserName 和 Department，这将生成两个列表——一个重复记录列表和一个唯一记录列表。
- 添加一个条件分割转换，我们将在此处编写验证规则。
- 为记录在 tbl_Errors 表中的错误记录添加适当的错误文本。
- 写入所有数据。
数据流
现在测试样本数据已加载到表中，我们将需要将数据合并到应用程序实际使用的名为 `tbl_UsersMerge` 的数据集中。
请确保您的系统上已安装/可用 Business Intelligence Projects——通常如果您的 SQL Server 安装选择了 Integration Services，就会安装。
在 Visual Studio 中，创建一个新项目，选择 Business Intelligence（项目类型），然后选择 Integration Services Project（模板）。
首先，将一个数据流任务拖到控制流工作表中。单击任务的文本区域可以更改其标签。
现在，双击该任务，这将打开此数据流任务的数据流工作表。
OLE DB 数据源
从“数据流源”中拖入一个 OLE DB 数据源，并双击它。这将打开 OLE DB 源编辑器，您将在其中创建一个新的连接（在连接管理器中）到您的测试数据库，以及到存储测试数据集的 tbl_Users 表。
接下来，在点击“列”后选择所有列。
查找转换
首先，理解查找转换的作用很重要——在其编辑器屏幕上会明确说明：……允许在输入和参考数据集之间执行简单的等值连接。
现在，从工具箱的“数据流转换”部分拖入一个查找，并将其放置在 OLE DB 数据源下方。单击数据源一次以激活红色和绿色箭头，然后将绿色箭头拖到查找，连接数据源和查找。
到目前为止，只有一个数据源连接到了查找。我们仍然需要连接参考数据集。双击查找转换，将其缓存模式属性设置为“完全缓存”。接下来，设置到数据库的连接，并使用表 tbl_UsersMerge——这是用于连接的第二个数据集。
接下来，我们将设置要连接的列。由于我们要检查重复项，不仅基于 UserName，还基于 UserName 和 Department，因此我们将连接这两个字段。
为此，请单击“列”，您将看到两个表——“可用输入”和“查找列”。
将 UserName 和 Department 列从“输入”拖到“查找”表，并通过单击“名称”列中的复选框来选择“查找列”中的所有复选框。
接下来单击“错误输出”。这部分有点棘手，比您最初期望的要复杂一些。
在“查找匹配输出”的“错误”列下，单击“失败组件”指示的位置，打开一个下拉菜单，然后选择“重定向行”。这样做会为该条件创建一个输出数据集。
条件转换
接下来拖入一个条件分割转换，并将查找的红色错误箭头连接到它。
双击“条件分割”，然后单击“输出名称”，创建一个名为“缺失数据”的新条件。输入（或直接复制/粘贴以下内容）在条件中
ISNULL([OLE DB Source].UserID) || ISNULL([OLE DB Source].Department) ||
LEN([OLE DB Source].Department) == 0 || ISNULL([OLE DB Source].UserName) ||
LEN([OLE DB Source].UserName) == 0
您也可以从左上角的列树中拖取列名，并查看右上角的有效运算符列表。
如您所见，我们正在执行多项验证检查。是否有字段为空或其长度为零？如果其中任何规则为真，这些匹配的行将被重定向到条件分割的绿色箭头，名为“缺失数据”。
这样，就处理了无效数据，那么有效数据呢？此输出是通过设置默认输出名称创建的——将其设置为“有效数据”。
有效数据
现在拖入一个 OLE DB 或 SQL Server 目标，并将条件分割的绿色“有效数据”箭头连接到它。使用 tbl_UsersMerge 作为数据目标，并在“映射”下，将可用输入中的匹配字段连接到可用目标列（UserID、UserName、Department）。
此步骤创建到输出数据库的连接，由于我们还有其他操作要执行，因此我们不想反复打开/关闭连接。
打开新连接的属性，并将“保留相同连接”设置为 true。
此时，我们可以在数据导入过程中获取有效数据，但错误日志记录呢？
查找匹配输出
请记住，我们只连接了查找转换的红色箭头。我们希望将错误类型记录到 tbl_Errors 表中，该表有两个额外的列：Error（列出错误文本）和 Timestamp，这是一个计算出的 `GetDate()` 字段。
查找转换的输出尚不包含 Error 字段。为了将此列添加到输出中，拖入一个派生列转换，将其命名为“添加重复数据错误文本”，并将查找转换的绿色箭头连接到它。
双击新的派生列转换，并添加“ErrorText”派生列，其表达式为“Duplicate Data”。这就是将与记录一起列出的错误文本。
现在，添加一个 OLE DB 或 SQL Server 目标，将此转换的绿色箭头连接到它，使用 tbl_Errors 作为连接的表。
在“映射”下，将查找和 ErrorText 字段连接到目标表。
这完成了重复错误日志记录，但我们仍然缺少缺失数据的错误日志记录。
缺失数据
我们在条件分割转换中进行了缺失数据检查，但只将有效数据连接到了目标。我们仍然希望记录无效数据所在的行。
添加一个新的派生列转换，将其命名为“添加缺失数据错误文本”，并将条件分割转换的绿色“缺失数据”箭头连接到它。
将 ErrorText 表达式设置为“Missing Data”。
最后，将一个新的 OLE DB 或 SQL Server 目标连接到派生列转换的绿色箭头，使用与重复错误目标相同的配置（使用 tbl_Errors，并映射列）。
执行
第一次运行（按 F5）将产生以下结果
您还可以通过右键单击箭头并选择“数据查看器/添加/网格”来在运行时查看数据。
第一次运行成功地将包含空部门值的行插入到错误表中，而其他九行有效数据则插入到合并表中。
再次运行包。
这次，您会注意到仍然有一个空值行被写入错误数据，但由于其他九行已写入合并表，因此它们被标记为错误/重复行并插入到错误表中。
关注点
您可能可以合并错误数据和重复错误，但由于我正在持久化数据库连接，我认为这并没有什么大不了的。
正如开发中很多事情一样，这可能不是最优的解决方案，我欢迎评论和建议，并希望这对某些人有所帮助。
历史
目前还没有。