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

输出进入脚本组件，其中包含平面文件中的三列作为输入，将三个表变量导入到脚本中，脚本组件中的五个输出分别具有相同的排除组编号和标记为同步输出的输入七个添加到输出中的新列（emp为每个输出3个，其中2个为工作，2个为办公室），并使用以下代码（必须添加System.xml.dll引用才能完成此操作工作。）：

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports System.Collections
Imports System.Data.OleDb

Public Class ScriptMain
    Inherits UserComponent

    Private da As New OleDbDataAdapter
    Private emp As New DataTable
    Private emph As New Hashtable()
    Private job As New DataTable
    Private jobh As New Hashtable()
    Private off As New DataTable
    Private offh As New Hashtable()
    Private maxempid As Integer
    Private maxjobid As Integer
    Private maxoffid As Integer

    Public Overrides Sub PreExecute()
        maxempid = 0
        maxjobid = 0
        maxoffid = 0
        da.Fill(emp, Me.Variables.EmpTab)
        For Each 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)
        For Each 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)
        For Each 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 Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

    If Not emph.ContainsKey(Row.EmployeeName) Then
        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
    If Not jobh.ContainsKey(Row.JobLevelHistory) Then
        maxjobid += 1
        jobh.Add(Row.JobLevelHistory, maxjobid)
        Row.JobId = maxjobid
        Row.Job2Id = maxjobid
        Row.DirectRowToJobTitle()
    Else
        Row.JobId = CInt(jobh.Item(Row.JobLevelHistory).ToString)
        Row.Job2Id = CInt(jobh.Item(Row.JobLevelHistory).ToString)
    End If
    If Not offh.ContainsKey(Row.OfficeHistory) Then
        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 Class

这个脚本的结果（该脚本为输入数据中的新值生成id，它通过将现有表加载到脚本的预先执行部分中的哈希表中，然后通过检查名称的存在并根据该名称增加maxid并将其添加到散列，如果它添加到散列，它还将该行添加到适当的（emp，job或off）输出，或从每个行的散列中检索maxid）。上面的状态将被写入剩下的两个输出（emp2job和emp2off）。