title: SSIS : Using multicast to enter data into 2 RELATED destinations
body: <p>I am new to SSIS.  I have data coming from a single source.  I need to enter that data into several tables (these tables are related by foreign key relationships).  I am using multicast to enter the data into several destinations.</p>
  
  <p>My question is...</p>
  
  <p>How do I get the identity of an entry into one destination and use that identity for the foreign key column of the 2nd destination?</p>
  
  <p>Here is an example of what I am looking for.  The Employee table has a foreign key to the Address table.  But the source includes all this information.  Entering the data into 2 different locations is easy.  But I need the identity from the Address table when I enter the info into the Employee table.  How do I get that Id?</p>
  
  <p><strong>Source (Excel file)</strong></p>
  
  <pre><code>Name        Street           State        etc...
  ----        ------           -----
  Jim         12345 Plain St.  CA
  Bob         54321 Main St.   CA
  etc.
  </code></pre>
  
  <p><strong>Destination</strong></p>
  
  <pre><code>   Address        Employee
     -------        -------
  PK AddressId   PK EmployeeId
     Street      FK AddressId
     State          Name
     etc...         etc...
  </code></pre>