title: SSIS with millions of data to compare from source and target
body: <p>I am trying to learn about SSIS and have few doubts regarding to that.</p>
  
  <p>I have want to compare 2 tables.1 table is in <code>Sql Server</code> and another is in <code>Oracle</code>.</p>
  
  <p>Both table will have same schema like below :</p>
  
  <pre><code>Sql Server:
  Id      Amount
  1       100
  2       200
  3       300
  
  
  Oracle:
  Id      Amount
  3       3000
  2       2000
  1       1000
  </code></pre>
  
  <p>This is just an few sample records as i have 24 millions records in source(12 millions) and target(12 millions) in  some random order.</p>
  
  <p><strong>Task</strong> : I am trying to compare source and target data.As there will always be 1 to 1 match between source and target data based on <code>joining id column from source and target</code> and doing comparision on <code>Amount column</code> and storing unmatch records in sql server database so i know <code>Look up transformation</code> will do in this case.</p>
  
  <p>But however i have some doubts :</p>
  
  <p>1) If i fire select * from query for source and target then where does 24 millions records will stay?
     In memory?</p>
  
  <p>2) Can i get memory exception in this case?</p>
  
  <p>3) As both the result set(i.e) are in different order in source and target how 
     does look up will work?
     Will it load all source data and then it will match 1 by 1 record in target 
     data by not loading whole    target data?</p>
  
  <p>4) How does SSIS handles millions of data comparision for source and target?</p>
  
  <p>Can anybody please help me clearing above doubts?</p>