title: How do I split flat file data and load into parent-child tables in database?
body: <p>I have denormalized data (coming from a file) that needs to be imported into parent-child tables.  The source data is something like this:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>What are the pieces, parts, or tactics of SSIS I should use to read the first three columns into a parent table, and the 4th column (Email) into a child table?  I have several options for the parent key which I am permitted to take:</p>
  
  <ul>
  <li>Directly use the Account# as the primary key</li>
  <li>Use a surrogate key generated by SSIS during the import process</li>
  <li>Configure an identity primary key</li>
  </ul>
  
  <p>I'm sure I've listed my primary key options in increasing order of difficulty.  I'd be interested in knowing how to do the first and the last option - I'll infer how to achieve the middle option.  To emphasize again, <em>I'm interested in a decidedly SSIS solution; I'm looking for an answer that uses the language of SSIS, rather than a procedural, technology neutral answer.</em></p>
  
  <p>My question is somewhat similar to <a href="https://cid-4114fe94ba584af5.office.live.com/edit.aspx/.Documents/Work2010?&amp;wd=target%28ClickSquared.one%7CAD681DBC-71A4-4AA8-A58D-90EA5FFCFF3F/Sovereign%20EState%7CA42C7230-D077-4680-831C-AE77404921E4/%29">another SO question</a>, having an answer of vague viability.  I'm hoping more detailed guidance could be given.  I already know how to solve this problem by creating a "staging" middle-step, where the parent-child separation is actually handled with straight SQL.  However, I'm curious about how this can be done without that kind of middle-step.  </p>
  
  <p>It seems to me this kind of import would be so common, that there would be a well-published formulaic way to handle it - a technique that SSIS excels at.  As yet, I've not quite seen any straight up answer to this.</p>
  
  <p><strong><code>Update #1</code></strong>: Based on comments, I've adjusted the sample data to be more obviously denormalized.  I also removed "flat" from "flat file," so that semantics don't interfere with the question.</p>
  
  <p><strong><code>Update #2</code></strong>: I've amplified my interest in a solution spoken in the language of SSIS.</p>