title: SSIS and CDC - Incorrect state at end of "Mark Processed Range"
body: <h1>The Problem</h1>
  
  <p>I currently have CDC running on a table named <strong>subscription_events</strong>.  The corresponding CT table is being populated with new inserts, updates, and deletes.</p>
  
  <p>I have two SSIS flows that move data from <strong>subscription_events</strong> into another table in a different database.  The first flow is the initial flow and has the following layout:</p>
  
  <p><img src="https://i.stack.imgur.com/l4Vsz.png" alt="enter image description here"></p>
  
  <p>The <strong>Import Rows Into Vertica</strong> step simply has a source and a destination and copies every row into another table.  As a note, the source table is currently active and has new rows flowing into it every few minutes.  The <strong>Mark Initial Load Start/End</strong> steps store the current state in a variable and that is stored in a separate table meant for storing CDC names and states.</p>
  
  <p>The second flow is the incremental flow and has the following layout:</p>
  
  <p><img src="https://i.stack.imgur.com/jHl4d.png" alt="enter image description here"></p>
  
  <p>The <strong>Import Rows Into Vertica</strong> step uses a CDC source and should pull the latest inserts, updates, and deletes from the CT table and these should be applied to the destination.  Here is where the problem resides; I <em>never</em> receive anything from the CDC source, even though there are new rows being inserted into the <strong>subscription_events</strong> table <em>and</em> the corresponding CT table is growing in size with new change data.</p>
  
  <p>To my understanding, this is how things should work:</p>
  
  <ol>
  <li>Mark Initial Load Start
  
  <ol>
  <li>CDC State should be <strong>ILSTART</strong></li>
  </ol></li>
  <li>Data Flow</li>
  <li>Mark Initial Load End
  
  <ol>
  <li>CDC State should be <strong>ILEND</strong></li>
  </ol></li>
  <li>Get Processing Range (First Run)
  
  <ol>
  <li>CDC State should be <strong>ILUPDATE</strong></li>
  </ol></li>
  <li>Data Flow</li>
  <li>Mark Processed Range (First Run)
  
  <ol>
  <li>CDC State should be <strong>TFEND</strong></li>
  </ol></li>
  <li>Get Processing Range (Subsequent Runs)
  
  <ol>
  <li>CDC State should be <strong>TFSTART</strong></li>
  </ol></li>
  <li>Data Flow</li>
  <li>Mark Processed Range (Subsequent Runs)
  
  <ol>
  <li>CDC State should be <strong>TFEND</strong></li>
  </ol></li>
  <li>Repeat the last three steps</li>
  </ol>
  
  <p>This is not how my CDC states are being set, though... Here are my states along the same process.</p>
  
  <ol>
  <li>Mark Initial Load Start
  
  <ol>
  <li>CDC State is <strong>ILSTART</strong></li>
  </ol></li>
  <li>Data Flow</li>
  <li>Mark Initial Load End
  
  <ol>
  <li>CDC State is <strong>ILEND</strong></li>
  </ol></li>
  <li>Get Processing Range (First Run)
  
  <ol>
  <li>CDC State is <strong>ILUPDATE</strong></li>
  </ol></li>
  <li>Data Flow</li>
  <li>Mark Processed Range (First Run)
  
  <ol>
  <li>CDC State is <strong>ILEND</strong></li>
  </ol></li>
  <li>Get Processing Range (Subsequent Runs)
  
  <ol>
  <li>CDC State is <strong>ILUPDATE</strong></li>
  </ol></li>
  <li>Data Flow</li>
  <li>Mark Processed Range (Subsequent Runs)
  
  <ol>
  <li>CDC State is <strong>ILEND</strong></li>
  </ol></li>
  <li>Repeat the last three steps</li>
  </ol>
  
  <p>I am never able to get out of the <strong>ILUPDATE/ILEND</strong> loop, so I am never able to get any new data from the CT table.  Why is this happening and what can I do to fix this?</p>
  
  <p>Thank you so much, in advance, for your help!  :)</p>
  
  <h1>Edit 1</h1>
  
  <p>Here are a couple of articles that <em>sort of</em> describe my situation, though not exactly.  They also did not help me resolve this issue, but it might help you think of something I can try.</p>
  
  <ul>
  <li><a href="http://www.bradleyschacht.com/understanding-the-cdc-state-value/" rel="nofollow noreferrer">http://www.bradleyschacht.com/understanding-the-cdc-state-value/</a></li>
  <li><a href="http://msdn.microsoft.com/en-us/library/hh231087.aspx" rel="nofollow noreferrer">http://msdn.microsoft.com/en-us/library/hh231087.aspx</a></li>
  </ul>
  
  <p>The second article includes this image, which shows the <strong>ILUPDATE/ILEND</strong> loop I am trapped in.</p>
  
  <p><img src="https://i.stack.imgur.com/pNQ0v.gif" alt="enter image description here"></p>
  
  <h1>Edit 2</h1>
  
  <p>Last week (May 26, 2014) I disabled then re-enabled CDC on the <strong>subscription_events</strong> table.  This didn't change anything, so I then disabled CDC on the entire database, re-enabled CDC on the database, and then enabled CDC on the <strong>subscription_events</strong> table.  This did make CDC work for a few days (and I thought the problem had been resolved by going through this process).  However, at the end of last week (May 30, 2014) I needed to re-load the entire table via this process, and I ran into the same problem again.  I'm still stuck in this loop and I'm not sure why or how to get out of it.</p>
  
  <h1>Edit 3</h1>
  
  <p>Before I was having this problem, I was having a separate issue which I posted about here: </p>
  
  <p><a href="https://stackoverflow.com/questions/23744083/cdc-is-enabled-but-cdc-dbotable-name-ct-table-is-not-being-populated">CDC is enabled, but cdc.dbo&lt;table-name&gt;_CT table is not being populated</a></p>
  
  <p>I am unsure if these are related, but figured it couldn't hurt to provide it.</p>