title: OLE DB Command in SSIS holds Exclusive lock on table indefinitely
body: <p>I'm implementing an upsert operation using SSIS</p>
  
  <p>The general package structure is </p>
  
  <pre><code>&lt;Begin transaction&gt;
  
  &lt;read config values&gt;
  
  &lt;Data flow task&gt;
  
  &lt;commit transaction&gt;
  </code></pre>
  
  <p>Data flow task is comprised of</p>
  
  <pre><code>&lt;oledb Source&gt;
  
  &lt;Lookup&gt;
  
  &lt;matched output to Update using oledbcommand&gt;
  
  &lt;No Match output to Insert using oledbcommand&gt;
  </code></pre>
  
  <p>The package runs fine the 1st time, when there are only inserts</p>
  
  <p>on the second run, when a set of updates have happened, and then an insert has happened, the Update statement ends up holding an exclusive lock on the destination table, goes to sleep with <code>AWAITING COMMAND</code>, while the Insert gets suspended and continues waiting for a lock.</p>
  
  <p>To check the statements in the last para, I used the following commands:</p>
  
  <pre><code>select * from master.sys.sysprocesses where blocked&lt;&gt;0 or spid in (select blocked from master.sys.sysprocesses where blocked &lt;&gt;0)
  </code></pre>
  
  <p>Is there a way to fix this locking situation?</p>