使用保持外键关系的SSIS加载多个表

Question

我正在尝试使用SSIS将数据从单个文件(包含million+记录)加载到SQL Server上的多个表中，同时保持文件中定义的关系。

为了更好地阐述示例，假设我正在尝试加载一个文件，其中包含员工姓名、他们过去担任过的办公室以及由制表符分隔的职位历史记录。

文件：

EmployeeName<tab>OfficeHistory<tab>JobLevelHistory
John Smith<tab>501<tab>Engineer
John Smith<tab>601<tab>Senior Engineer
John Smith<tab>701<tab>Manager
Alex Button<tab>601<tab>Senior Assistant
Alex Button<tab>454<tab>Manager

如果我的Office数据库架构包含以下表：

Employee (nId, name)
Office (nId, number)
JobTitle (nId, titleName)
Employee2Office (nEmpID, nOfficeId)
Employee2JobTitle (nEmpId, nJobTitleID)

如何使用SSIS将文件加载到上述模式中自动生成员工、办公室和JobTitle的ID，并维护员工和办公室以及员工和职称之间的关系？

所以在这种情况下。这些表应该如下所示：

Employee
1 John Smith
2 Alex Button

Office
1 501
2 601
3 701
4 454

JobTitle
1 Engineer
2 Senior Engineer
3 Manager
4 Senior Assistant

Employee2Office
1 1
1 2
1 3
2 2
2 4

Employee2JobTitle
1 1
1 2
1 3
2 4
2 3

我是SSIS的新手，在执行数据流任务时没有尝试过自动生成I和建立外键关系。如有任何建议，我们将不胜感激。

谢谢!

Answer 1

一个有趣的问题。下面是我将如何做的(Sql Server2005)。(我假设这是每月一次的工作，而不仅仅是一次，所以我添加了代码以实现可重复性。)

1. 为Employee、JobTitle和Office表创建三个变量(type =Object)
2. 使用三个sql任务将这三个表中的行选择到相应的变量中。
3. 添加数据流任务。使用平面文件从平面文件中选择
   1. 进入脚本组件，其中平面文件中的三列作为输入，三个表变量导入到脚本中，脚本组件中的五个输出都具有相同的排除组号，并且该输出的输入标记为同步。向输出中添加了7个新列(3个用于emp，1个用于每个输出，2个用于作业，2个用于办公室)，并添加了以下代码(必须添加对System.xml.dll的引用才能使所有内容正常工作)：

导入系统导入System.Data导入System.Math导入Microsoft.SqlServer.Dts.Pipeline.Wrapper导入Microsoft.SqlServer.Dts.Runtime.Wrapper导入System.Collections导入System.Data.OleDb公共类ScriptMain继承UserComponent私有da作为新的OleDbDataAdapter私有emp作为新的DataTable私有emp作为新的哈希表()私有作业作为新的DataTable私有作业作为新的哈希表()私有off作为新的DataTable私有off作为新的哈希表()私有maxempid作为整数私有maxjobid作为整数私有maxoffid作为整数公共重写子PreExecute() maxempid =0 maxjobid =0 maxoffid =0 da.Fill(emp，Me.Variables.EmpTab)对于每个dr As DataRow In emp.Rows emph.Add(dr.Item("Name")，dr.Item("nID")) If (CInt(dr.Item("nID").ToString) > maxempid) Then maxempid = CInt(dr.Item("nID").ToString) End If Next da.Fill(job，Me.Variables.JobTab)对于每个dr As DataRow In job.Rows jobh.Add(dr.Item("titleName")，dr.Item("nID")) If (CInt(dr.Item("nID").ToString) > maxempid) Then maxjobid = CInt(dr.Item("nID").ToString) End If Next da.Fill(off，Me.Variables.OffTab)对于每个dr As DataRow In off.Rows offh.Add(dr.Item("number")，dr.Item("nID")) If (CInt(dr.Item("nID").ToString) > maxempid) Then maxoffid = CInt(dr.Item("nID").ToString) End If Next emp.Dispose() job.Dispose() off.Dispose() da.Dispose() MyBase.PreExecute() End Sub Public重写子Input0_ProcessInputRow(ByVal行作为Input0Buffer)如果不是emph.ContainsKey(Row.EmployeeName)，则maxempid += 1 emph.Add(Row.EmployeeName，maxempid) Row.EmpId = maxempid Row.Emp2Id = maxempid Row.Emp3Id = maxempid Row.DirectRowToEmployee() Else Row.EmpId = CInt(emph.Item(Row.EmployeeName).ToString) Row.Emp2Id = CInt(emph.Item(Row.EmployeeName).ToString) Row.Emp3Id = CInt(emph.Item(Row.EmployeeName).ToString) End If Not ()然后maxjobid += 1 jobh.Add(Row.JobLevelHistory，maxjobid) Row.JobId = maxjobid Row.Job2Id = maxjobid Row.DirectRowToJobTitle()否则Row.JobId = CInt(jobh.Item(Row.JobLevelHistory).ToString) Row.Job2Id = CInt(jobh.Item(Row.JobLevelHistory).ToString) End If Not offh.ContainsKey(Row.OfficeHistory)则maxoffid += 1 offh.Add(Row.OfficeHistory，maxoffid) Row.OffId = maxoffid Row.Off2Id = maxoffid Row.DirectRowToOfficeNumber() Else Row.OffId = CInt(offh.Item(Row.OfficeHistory).ToString) Row.Off2Id = CInt(offh.Item(Row.OfficeHistory).ToString) End If Row.DirectRowToEmp2Job() Row.DirectRowToEmp2Off() End Sub End类

• 此脚本的结果(脚本为输入数据中的新值生成id。为此，它将现有表加载到脚本的preexecute部分的哈希表中，然后检查名称是否存在，并根据该名称递增maxid并将其添加到哈希中(如果将其添加到哈希中，则还会将该行添加到适当的(emp、job或off)输出中，或者从每行的哈希中检索maxid。)无论上述状态如何，所有行都将写入其余两个输出(emp2job和emp2off).

• Next，发送要查找的数据流)(要检查目标表中的现有行，然后是目标oledb连接器( emp、job和off选中标识插入复选框，emp2job和emp2off取消选中oledb

Answer 2

如果确定要加载的数据具有引用完整性，则可以在脚本任务中禁用外键约束条件，然后使用并行数据加载执行数据流，并在数据加载完成后再次启用约束条件。如果数据有问题，操作将失败。不过，您必须设计回滚或清理策略。

另一种选择是以串行方式加载数据，从主表开始，到子表结束。我认为这是“更安全”的选择，因为它不会将您的数据完整性暴露给在ETL加载时可能正在使用这些表的其他用户。我更喜欢这个选项。

Answer 3

下面是如何解释的--只用文本解释有点困难，但我会试一试：

使用identity列在数据库中定义employee、office和job title表，以便自动生成ID。

定义没有的多对多表(不需要或不需要标识)

在您的SSIS数据流中，为了首先在数据库中建立ID，然后返回并插入多对多行，您必须在两次遍历中完成此操作。

创建一个数据流：

1. 将一个要读取的数据源放入文件
2. 中，并使用多播将其拆分为三个副本。一个是给员工的，一个是办公室的，一个是头衔的。
3. 为每个人放了一个排序(这通常是一个禁忌，但由于来源是文本而不是数据库，我们必须去那里)。将排序设置为仅通过三个字段中的一个，并选中排序中的选项以删除重复项。这将为每个基表创建一个唯一的列表(类似于select distinct)。
4. 为三个基表中的每一个都放置一个目标，并将其连接到每个表。

在第一个数据流之后，添加第二个数据流。这将填充多对多关系行

1. 读取带有数据源的文件
2. 添加一个查找，查找数据库中的雇员姓名并返回雇员ID，这将得到上面生成的雇员ID。(这通常称为按业务键或自然键查找代理键)
3. 添加一个在数据库中查找标题并返回标题ID的查找
4. 添加一个在数据库中查找办公室并再次返回办公室ID
5. 的查找，将结果多播为两个副本，一个用于employee-office，另一个用于按您需要的逻辑排序，可能会再次使用排序，要对这些数据进行重复数据消除(取决于如何从输入中规范化的细节)，
6. 会将结果放入具有两个destinations.

的多对多表中