title: ssis Data Migration - Master Detail records with new surrogate keys
body: <p>Finally reached data migration part  of my Project and now trying to move data from MySQL to SQL Server.</p>
  
  <p>SQL Server has new schema (mapping is not always one to one).</p>
  
  <p>I am trying to use SSIS  for the conversion, which I started learning today morning. </p>
  
  <p>We have customer and customer location table in MySQL and equivalent table in SQL Server. In SQL server all my tables now have surrogate key column (GUID) and I am creating the same in Script Component.</p>
  
  <p><strong>Also note that I do have a primary key in current mysql tables.</strong></p>
  
  <p>What I am looking for is how I can add child records  to customer location table with newly created guid as parent key.</p>
  
  <p>I see that SSIS have Foreach loop container, is this of any use here.</p>
  
  <p>if not  another possibility that I can think of is create two Data Flow Task and [somehow] just before the master data is sent to Destination Component [Table]  on primary dataflow task , add  a variable with newly created GUID and another with old PrimaryID, which will be used to create source for DataTask Flow for child records.</p>
  
  <p>May be to simplyfy , this can also be done once datatask for master is complete and then datatask for child reads this master data and  inserts  child records  from MySQL to SQL Server  table. This would though mean that I have to load all my parent table records back into memory.</p>
  
  <p>I know this is all too confusing and it is mainly because I am very confused :-(, to bear with me and if you want more information let me know.</p>
  
  <p>I have been through may links that i found through google search but none of them really explains( or I was not able to uderstand)  how the process is carried out.</p>
  
  <p>Please advise</p>
  
  <p>regards,</p>
  
  <p>Mar</p>
  
  <p>** Edit 1**</p>
  
  <p>after further searching and refining key words i found this link in SO and going through it to see if it can be used in my scenario</p>
  
  <p><a href="https://stackoverflow.com/questions/6143433/how-to-load-parent-child-data-found-in-edi-823-lockbox-file-using-ssis">How to load parent child data found in EDI 823 lockbox file using SSIS?</a></p>