title: SSIS - Delete Existing Rows then Insert, Incomplete Result
body: <p>I'm relatively new to SSIS and know that handling duplicates is an oft repeated question, so thank you in advance for reading through my wall of text and for any help with my complicated situation.</p>
  
  <p>I have a small 18179 row table (we'll call it Destination) that needs to be updated with SSIS using a flat file. The 18179 row flat file I am testing contains only records that exist in Destination and have changed. Currently, I have a package that loads a staging table (we'll call it Stage) from the flat file, then moves to the Data Flow and Look Up </p>
  
  <p><img src="https://i.stack.imgur.com/Ny7h7.png" alt="enter image description here"></p>
  
  <p>This Data Flow takes Stage and does Look Up LKP_OrderID from Stage on Destination using primary key OrderID to see if the record exists.
  If the OrderID <em>does not</em> exist in Destination, then it follows the New OrderID path and the record is inserted into Destination at DST_OLE_Dest.</p>
  
  <p>Here is where I am having trouble: If the OrderID <em>does</em> exist in Destination, then it follows the Existing OrderID path. The CMD_Delete_Duplicates OLE DB Command executes:</p>
  
  <pre><code>DELETE d
  FROM dbo.Destination d
      INNER JOIN dbo.Stage s ON d.OrderID = s.OrderID
  </code></pre>
  
  <p>This should delete any records from Destination that exist in Stage. Then it should insert the updated version of those records from Stage at DST_OLE_Desti.
  However, it seems to process the 18179 rows in 2 batches: in the first batch it processes 9972 rows.</p>
  
  <p><img src="https://i.stack.imgur.com/RWb0F.png" alt="first batch"></p>
  
  <p>Then, in the 2nd batch it processes the remaining 8207 rows. It displays that it inserted all 18179 rows to Destination, but I only end up with the last batch of 8207 rows in Destination.</p>
  
  <p>I believe it deletes and inserts the 1st batch of 9972 rows, then runs the above delete from inner join SQL again for the 2nd batch of 8207 rows, inadvertently deleting the just-inserted 9972 rows and leaving me with the 8207.</p>
  
  <p>I've found that maximizing DefaultBufferSize to 104857600 bytes and increasing the DefaultBufferMaxRows in the Data Flow such that the package processes all 18179 rows at once correctly deletes and inserts all 18179, but once my data exceeds the 104857600 file size, this will again be an issue. I can also use the OLE DB Command transformation to run</p>
  
  <pre><code>DELETE FROM dbo.Destination WHERE OrderID = ?
  </code></pre>
  
  <p>This should pass OrderID from Stage and delete from Destination where there is a match, but this is time intensive and takes ~10 minutes for this small table. Are there any other solutions out there for this problem? How would I go about doing an Update rather than an Insert and Delete if that is a better option?</p>