title: Best incremental load method using SSIS with over 20 million records
body: <p><strong>What is needed</strong>: I'm needing 25 million records from oracle incrementally loaded to SQL Server 2012. It will need to have an UPDATE, DELETE, NEW RECORDS feature in the package. The oracle data source is always changing. </p>
  
  <p><strong>What I have:</strong> I've done this many times before but not anything past 10 million records.First I have an [Execute SQL Task] that is set to grab the result set of the [Max Modified Date]. I then have a query that only pulls data from the [ORACLE SOURCE] > [Max Modified Date] and have that lookup against my destination table.</p>
  
  <p>I have the the [ORACLE Source] connecting to the [Lookup-Destination table], the lookup is set to NO CACHE mode, I get errors if I use partial or full cache mode because I assume the [ORACLE Source] is always changing. The [Lookup] then connects to a [Conditional Split] where I would input an expression like the one below.</p>
  
  <pre><code>(REPLACENULL(ORACLE.ID,"") != REPLACENULL(Lookup.ID,"")) 
  || (REPLACENULL(ORACLE.CASE_NUMBER,"") 
  != REPLACENULL(ORACLE.CASE_NUMBER,""))
  </code></pre>
  
  <p>I would then have the rows that the [Conditional Split] outputs into a staging table. I then add a [Execute SQL Task] and perform an UPDATE to the DESTINATION-TABLE with the query below:</p>
  
  <pre><code> UPDATE Destination
   SET SD.CASE_NUMBER =UP.CASE_NUMBER,
       SD.ID           =   UP.ID,
   From Destination SD
   JOIN  STAGING.TABLE UP
      ON UP.ID = SD.ID
  </code></pre>
  
  <p><strong>Problem:</strong> This becomes very slow and takes a very long time and it just keeps running. How can I improve the time and get it to work? Should I use a cache transformation? Should I use a merge statement instead? </p>
  
  <p>How would I use the expression REPLACENULL in the conditional split when it is a data column? would I use something like :</p>
  
  <pre><code>(REPLACENULL(ORACLE.LAST_MODIFIED_DATE,"01-01-1900 00:00:00.000") 
   != REPLACENULL(Lookup.LAST_MODIFIED_DATE," 01-01-1900 00:00:00.000")) 
  </code></pre>
  
  <p>PICTURES BELOW:</p>
  
  <p><img src="https://i.stack.imgur.com/V5qWb.png" alt="Date Flow"></p>
  
  <p><img src="https://i.stack.imgur.com/nIkwQ.png" alt="Control flow"></p>