title: How do I optimize Upsert (Update and Insert) operation within SSIS package?
body: <p>I am not a DBA but I do work for a small company as the IT person.  I have to replicate a database from staging to production.  I have created an SSIS package to do this but it takes hours to run.  This isn't a large data warehouse type of project, either, it's a pretty straightforward <code>Upsert</code>.  I'm assuming that I am the weak link in how I designed it.</p>
  
  <p>Here's my procedure:</p>
  
  <ol>
  <li>Truncate staging tables (<code>EXECUTE SQL TASK</code>)</li>
  <li>Pull data from a development table into staging (<code>Data Flow Task</code>)</li>
  <li>Run a data flow task
  <ol>
  <li><em><code>OLE DB Source</code></em></li>
  <li><em><code>Conditional Split Transformation</code></em> (Condition used: <code>[!]ISNULL(is_new_flag)</code>)</li>
  <li>If new insert, if existing update</li>
  </ol></li>
  </ol>
  
  <p>The data flow task is mimicked a few times to change tables/values but the flow is the same.  I've read several things about OLE DB components being slow to updates being slow and have tried a few things but haven't gotten it to run very quickly.</p>
  
  <p>I'm not sure what other details to give, but I can give anything that's asked for.</p>