title: Loading Multiple Tables using SSIS keeping foreign key relationships
body: <p>I am trying to load data from a single file (with million+ records) into multiple tables on SQL Server using SSIS while maintaining the relationships defined in the file. </p>
  
  <p>To better elaborate with an example, lets assume I am trying to load a file containing employee name, the offices they have occupied in the past and their Job title history separated by a tab.</p>
  
  <p>File:</p>
  
  <pre><code>EmployeeName&lt;tab&gt;OfficeHistory&lt;tab&gt;JobLevelHistory
  John Smith&lt;tab&gt;501&lt;tab&gt;Engineer
  John Smith&lt;tab&gt;601&lt;tab&gt;Senior Engineer
  John Smith&lt;tab&gt;701&lt;tab&gt;Manager
  Alex Button&lt;tab&gt;601&lt;tab&gt;Senior Assistant
  Alex Button&lt;tab&gt;454&lt;tab&gt;Manager
  </code></pre>
  
  <p>If my Office database schema has the following tables:</p>
  
  <pre><code>Employee (nId, name)
  Office (nId, number)
  JobTitle (nId, titleName)
  Employee2Office (nEmpID, nOfficeId)
  Employee2JobTitle (nEmpId, nJobTitleID)
  </code></pre>
  
  <p>How can I use SSIS to load the file into the schema above Auto-Generating IDs for Employee, Office and JobTitle and maintaining the relationship between the employee and offices, and employee and Job Titles?</p>
  
  <p>So in this case. the tables should look like:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>I am a newbie to SSIS and have not played around with auto-generating IDs and establishing foreign key relationships while executing a Data Flow Task. Any pointers would be appreciated.</p>
  
  <p>Thanks!</p>