sql-server - 如何拆分平面文件数据并加载到数据库的父子表中？

Question

我有非规范化的数据(来自文件)，需要导入到父子表中。源数据是这样的:

Account#    Name        Membership    Email
101         J Burns     Gold          alpha@foo.com
101         J Burns     Gold          bravo@foo.com
101         J Burns     Gold          charlie@yay.com
227         H Gordon    Silver        red@color.com
350         B Clyde     Silver        italian@food.com
350         B Clyde     Silver        mexican@food.com

我应使用SSIS的哪些部分，部分或策略，将前三列读入父表，将第四列(电子邮件)读入子表？对于父 key ，我有几种选择，可以选择:

直接使用Account＃作为主键

在导入过程中使用SSIS生成的代理 key

配置身份主键

我确定我已经按照难度递增的顺序列出了我的主键选项。我想知道如何做第一个和最后一个选项-我会推断如何实现中间选项。再次强调一下，我对SSIS解决方案非常感兴趣。我正在寻找使用SSIS语言的答案，而不是过程性，技术中立的答案。

我的问题有点类似于 another SO question，答案是模糊的生存能力。我希望可以提供更详细的指导。我已经知道如何通过创建“分段”中间步骤来解决此问题，该中间步骤实际上是通过直接SQL处理父子分离的。但是，我很好奇在没有这种中间步骤的情况下如何做到这一点。

在我看来，这种导入非常普遍，以至于将有一种公开发表的公式化方法来处理它-SSIS擅长的一种技术。到目前为止，我还没有看到对此的任何直截了当的答案。

Update #1 :基于注释，我已经对样本数据进行了调整，以使其更明显地被归一化。我还从“平面文件”中删除了“平面”，以便语义不会干扰该问题。

Update #2 :我对用SSIS语言说的解决方案更加感兴趣。

Answer 1

在加载父子数据时，可以考虑以下一种可能的选择。此选项包括两个步骤。在的第一步步骤中，读取源文件并将数据写入父表。在的第二个步骤中，再次读取源文件，并使用查找转换来获取父信息，以便将数据写入子表。以下示例使用问题中提供的数据。本示例是使用SSIS 2008 R2和SQL Server 2008数据库创建的。

分步过程:

创建一个名为Source.txt的样本平面文件，如屏幕快照＃ 1 所示。

在SQL数据库中，使用 SQL脚本部分下给出的脚本创建两个名为dbo.Parent和dbo.Child的表。这两个表都有一个自动生成的标识列。

在软件包上，放置OLE DB connection以连接到SQL Server，并放置Flat File connection读取源文件，如屏幕快照＃ 2 所示。如屏幕截图所示配置平面文件连接＃ 3 -＃ 9 。

在“控制流”选项卡上，放置两个Data Flow Tasks，如屏幕快照＃ 10 所示。

在名为父的数据流任务内部，放置一个平面文件源，排序转换和OLE DB目标，如屏幕快照＃ 11 所示。

配置平面文件源，如屏幕快照＃ 12 和＃ 13 所示。我们需要阅读平面文件源。

如截图＃ 14 所示，配置排序转换。我们需要消除重复的值，以便仅将唯一记录插入到父表dbo.Parent中。

配置ole db目标，如屏幕快照＃ 15 和＃ 16 所示。我们需要将数据插入父表dbo.Parent中。

在名为子的数据流任务内部，放置一个平面文件源，查找转换和OLE DB目标，如屏幕快照＃ 17 所示。

配置平面文件源，如屏幕快照＃ 12 和＃ 13 所示。此配置与上一个数据流任务中的平面文件源相同。

配置查询转换，如屏幕快照＃ 18 和＃ 20 所示。我们需要使用文件中存在的其他键列从表dbo.Parent中找到父ID。此处的关键列是“帐户”，“名称”和“电子邮件”。如果文件碰巧具有唯一列，则可以仅使用该列来获取父ID。

配置ole db目标，如屏幕快照＃ 21 和＃ 22 所示。我们需要将Email列和Parent ID一起插入到表dbo.Child中。

屏幕快照＃ 23 在包执行之前显示表中的数据。

屏幕截图＃ 24 和＃ 25 显示示例软件包执行。

屏幕快照＃ 26 在执行包之后，在表中显示数据。

希望能有所帮助。

SQL脚本:

CREATE TABLE [dbo].[Child](
    [ChildId] [int] IDENTITY(1,1) NOT NULL,
    [ParentId] [int] NULL,
    [Email] [varchar](21) NULL,
CONSTRAINT [PK_Child] PRIMARY KEY CLUSTERED ([ChildId] ASC)) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Parent](
    [ParentId] [int] IDENTITY(1,1) NOT NULL,
    [Account] [varchar](12) NULL,
    [Name] [varchar](12) NULL,
    [Membership] [varchar](14) NULL,
CONSTRAINT [PK_Parent] PRIMARY KEY CLUSTERED ([ParentId] ASC)) ON [PRIMARY]
GO

截图＃1:

截屏2:

截屏＃3:

截图＃4:

截屏5:

截图＃6:

截屏7:

截屏＃8:

截屏＃9:

截图＃10:

截屏11:

截屏＃12:

截屏＃13:

截图＃14:

截屏＃15:

截屏＃16:

截屏17:

截屏＃18:

截图＃19:

截图＃20:

截图＃21:

截图＃22:

截图＃23:

截图＃24:

截图＃25:

截图＃26: