SSIS 导入文本文件（带验证）

Math08avan

5.00/5 (5投票s)

2015 年 9 月 6 日

CPOL

8分钟阅读

21014

479

使用外键关系和验证将文本文件数据导入 SQL 数据库。

引言

在本技巧中，我们将学习如何使用 SSIS 导入具有外键关系和验证的文本文件。所有错误记录都将存储在单独的表中。

背景

在开始本技巧之前，我建议您阅读上一篇文章：SSIS - 导入文本文件

使用工具和代码

数据库：SQL Server 2012 R2
BI：SQL Server Data Tools 2010

创建示例文件
创建数据库表
创建 SSIS 包
示例文件分析和预期输出
最终输出

开始吧

第一部分：创建示例文件

1. 在此示例中，我们将使用一个学生信息文本文件。

学生姓名、注册号和系名称是列。

第二部分：创建数据库表

1. 打开 SQL Server Management Studio

2. 创建一个名为 "TestDB" 的数据库

3. 创建一个名为 "SSIS_Department" 的表，包含以下列

DepartmentId
DepartmentName

将 DepartmentId 设置为主键，并将 Identity Specification 设置为 "yes"

CREATE TABLE [dbo].[SSIS_Department](
	[DepartmentId] [bigint] IDENTITY(1,1) NOT NULL,
	[DepartmentName] [varchar](10) NOT NULL,
 CONSTRAINT [PK_SSIS_Department] PRIMARY KEY CLUSTERED 
(
	[DepartmentId] ASC
)
) ON [PRIMARY]

4. 创建一个名为 "SSIS_Student" 的表，包含以下列

StudentId
StudentName
RegisterNo
DepartmentId

将 StudentId 设置为主键，并将 Identity Specification 设置为 "yes"

将 SSIS_Department 表的 DepartmentId 设置为 SSIS_Student 表的 DepartmentId 的外键

CREATE TABLE [dbo].[SSIS_Student](
	[StudentId] [bigint] IDENTITY(1,1) NOT NULL,
	[StudentName] [varchar](50) NOT NULL,
	[RegisterNo] [varchar](5) NOT NULL,
	[DepartmentId] [bigint] NOT NULL,
 CONSTRAINT [PK_SSIS_Student] PRIMARY KEY CLUSTERED 
(
	[StudentId] ASC
)
) ON [PRIMARY]

ALTER TABLE [dbo].[SSIS_Student]  WITH CHECK ADD  CONSTRAINT [FK_SSIS_Student_SSIS_Department] FOREIGN KEY([DepartmentId])
REFERENCES [dbo].[SSIS_Department] ([DepartmentId])

ALTER TABLE [dbo].[SSIS_Student] CHECK CONSTRAINT [FK_SSIS_Student_SSIS_Department]

第三部分：创建 SSIS 包

1. 启动 **SQL Server Data Tools (Microsoft Visual Studio 2010 Shell)**

2. 转到 **文件 -> 新建 -> 项目**

3. 在 "已安装模板" 选项中选择 **Business Intelligence**，然后选择 **Integration Services Project** 作为项目类型，并为项目命名。

4. 将两个数据流任务从工具箱拖到“控制流”选项卡。为便于理解，将第一个数据流任务重命名为 **"Department"**，将第二个数据流任务重命名为 **"Student"**。

5. 创建一个平面文件连接管理器以读取文本文件，并创建一个 OLE DB 连接管理器以将文本文件数据插入数据库。右键单击“连接管理器”窗格，然后选择“新建平面文件连接”。

平面文件连接管理器“常规”设置：指定连接管理器名称，指定之前创建的示例文件位置，其余设置保持不变。

平面文件连接管理器“列”设置：列设置将显示示例文件中的所有可用列。

平面文件连接管理器“高级”设置：在高级设置中，我们可以添加验证。这里我们将为 RegisterNo 列添加验证。选择 RegisterNo 列，并将 OutputColumnWidth 设置为 5。

Preview（预览）用于查看示例文件。然后单击 OK 创建连接管理器。

6. 现在，我们来创建 OLE DB 连接管理器。右键单击“连接管理器”窗格，创建一个新的 OLE DB 连接。

在连接管理器窗口中指定服务器名称。
由于我使用的是本地服务器，所以我使用了 **"."**，否则需要指定服务器名称。
选择 **"使用 SQL Server 身份验证"** 选项，并提供用户名和密码。
在 **"选择或输入数据库名称"** 中，选择 **"TestDB"** 数据库。我们之前已在 TestDB 数据库中创建了表。
通过“测试连接”按钮验证连接是否成功建立。

最后，单击 OK 按钮创建 OLE DB 连接管理器。

7. 在此示例中，我们将从文本文件加载学生数据。但在我们的数据库设计中，SSIS_Student 表使用 DepartmentId。这意味着将文本文件中所有唯一的系名称（DepatmentName）插入 SSIS_Department 表，然后使用查找函数查找 DepartmentName 对应的 DepartmnetId，再插入学生详细信息。

8. 我们之前已包含一个验证：RegisterNo 限制为五个字符。如果一条记录的 RegisterNo 超过五个字符，它将被加载到错误数据表中。

9. 好了，让我们完成部门部分。

右键单击“Department”数据流任务并选择“编辑”。它将进入数据流选项卡。
现在从工具箱放置一个平面文件源。右键单击平面文件源并选择“编辑”。它将显示平面文件源编辑器。

在连接管理器设置中：在下拉列表中选择平面文件连接管理器名称。
在列设置中：它将显示所有可用的外部列。现在我们只处理系名称。所以选择 DepartmentName。

最后单击 OK 按钮。

10. 现在放置一个排序转换（Sort Transformation）来自 SSIS 工具箱。将平面文件源的输出连接到排序转换的输入。

右键单击排序转换并选择“编辑”。它将显示排序转换编辑器。指定任何排序类型和排序顺序，并勾选“删除具有重复排序值的行”复选框。

最后单击 OK 按钮。

11. 现在放置一个 OLE DB 目标（OLE DB Destination）以插入系名称。

右键单击 OLE DB 目标并选择“编辑”。它将显示 OLE DB 目标编辑器。
在连接管理器设置中：从下拉列表中选择 **“OLE DB 连接管理器”** 名称，将 **“数据访问模式”** 设置为 **“表或视图 - 快速加载”**，将 **“表或视图的名称”** 设置为 **“SSIS_Department”**。

在映射设置中：将相应的输入列映射到目标列。

最后单击 OK 按钮。

12. 现在我们将开始学生详细信息部分。

转到“控制流”选项卡，右键单击“Student”数据流任务并选择“编辑”。它将进入“Student”数据流选项卡。
现在从工具箱放置一个平面文件源。右键单击平面文件源并选择“编辑”。它将显示平面文件源编辑器。
在连接管理器设置中：在下拉列表中选择平面文件连接管理器名称。
在列设置中：它将显示所有可用的外部列。选择所有可用的外部列。

在错误输出设置中：将所有列的 **错误** 和 **截断** 设置为 **重定向行**。

最后单击 OK 按钮。

13. 这次平面文件源有两个输出。蓝色表示成功记录，红色表示错误记录。

现在放置一个 OLE DB 目标以插入错误记录。将其重命名为“Error Destination”。将平面文件源的 **红色** 输出连接到错误目标的输入。

右键单击错误目标并选择“编辑”。它将显示 OLE DB 目标编辑器。
在连接管理器设置中：从下拉列表中选择 **OLE DB 连接管理器** 名称，将 **数据访问模式** 设置为“**表或视图 - 快速加载**”。在“**表或视图的名称**”下拉列表中，我们需要选择用于插入错误记录的表，但我们还没有创建任何表来插入错误记录。

要插入错误记录，请单击“表或视图的名称”下拉列表旁边的“新建...”按钮。它将显示用于插入错误记录的表结构。

复制代码，并使用 SQL Management Studio 创建一个表。同时包含一个主键字段。

CREATE TABLE [SSIS_Error] (
    [ErrorId] [bigint] IDENTITY(1,1) NOT NULL,
    [Flat File Source Error Output Column] varchar(max),
    [ErrorCode] int,
    [ErrorColumn] int,
	CONSTRAINT [PK_SSIS_Error] PRIMARY KEY CLUSTERED 
(
	[ErrorId] ASC
)
)

创建“SSIS_Error”表后，将“**表或视图的名称**”下拉列表设置为“SSIS_Error”。

在映射设置中：将相应的可用输入列映射到可用目标列。

最后单击 OK 按钮。

14. 我们已经完成了错误记录捕获部分。现在我们将处理成功记录。

为了处理成功记录，我们需要进行一个查找转换（lookup transformation）来查找 DepartmentName 对应的 DepartmentId。
从工具箱放置一个查找转换，并将平面文件源的蓝色输出连接到查找转换的输入。

右键单击查找转换并选择“编辑”。它将显示查找转换编辑器。
在常规设置中：选择 **缓存模式** 为“**完整缓存**”，**连接类型** 为“**OLE DB 连接管理器**”。

在连接设置中：从下拉列表中选择 **OLE DB 连接管理器** 名称，并将 **使用表或视图** 设置为 **SSIS_Department** 表。

在列设置中：将可用的输入列“DepartmentName”映射到可用的查找列“DepartmentName”，并勾选可用的查找列“DepartmentId”。

最后单击 OK 按钮。

15. 现在放置一个 OLE DB 目标，并将其名称更改为“Student Destination”。将查找匹配输出连接到 Student Destination 输入。

在连接管理器设置中：从下拉列表中选择 **OLE DB 连接管理器** 名称，将 **数据访问模式** 设置为“**表或视图 - 快速加载**”。在 **“表或视图的名称”** 下拉列表中，选择 “**SSIS_Student**”。

在映射设置中：将相应的输入列映射到目标列。

最后单击 OK 按钮。

第四部分：示例文件分析和预期输出

我们已经完成了 SSIS 包的设计。在执行此包之前，请看一下我们的输入示例文件。

在我们的包中，部门部分将首先执行。
因此，CSE、ECE、EEE 和 IT 数据将被插入 SSIS_Department 表。（四条记录）
然后将执行学生部分。
在处理学生记录时，我们为 RegisterNo 设置了长度限制为五个字符。但在我们的示例文件中，最后一条记录（Watson）的 RegisterNo 超出了限制。因此，该记录不应加载到 SSIS_Student 表中。它应该在 SSIS_Error 表中（一条记录）。
其余的学生记录将与 DepartmentId 一起插入到 SSIS_Student 表中。（四条记录）