SSIS and CDC - Incorrect state at end of "Mark Processed Range"

Question

The Problem

I currently have CDC running on a table named subscription_events. The corresponding CT table is being populated with new inserts, updates, and deletes.

I have two SSIS flows that move data from subscription_events into another table in a different database. The first flow is the initial flow and has the following layout:

The Import Rows Into Vertica 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 Mark Initial Load Start/End steps store the current state in a variable and that is stored in a separate table meant for storing CDC names and states.

The second flow is the incremental flow and has the following layout:

The Import Rows Into Vertica 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 never receive anything from the CDC source, even though there are new rows being inserted into the subscription_events table and the corresponding CT table is growing in size with new change data.

To my understanding, this is how things should work:

1. Mark Initial Load Start
   1. CDC State should be ILSTART
2. Data Flow
3. Mark Initial Load End
   1. CDC State should be ILEND
4. Get Processing Range (First Run)
   1. CDC State should be ILUPDATE
5. Data Flow
6. Mark Processed Range (First Run)
   1. CDC State should be TFEND
7. Get Processing Range (Subsequent Runs)
   1. CDC State should be TFSTART
8. Data Flow
9. Mark Processed Range (Subsequent Runs)
   1. CDC State should be TFEND
10. Repeat the last three steps

This is not how my CDC states are being set, though... Here are my states along the same process.

1. Mark Initial Load Start
   1. CDC State is ILSTART
2. Data Flow
3. Mark Initial Load End
   1. CDC State is ILEND
4. Get Processing Range (First Run)
   1. CDC State is ILUPDATE
5. Data Flow
6. Mark Processed Range (First Run)
   1. CDC State is ILEND
7. Get Processing Range (Subsequent Runs)
   1. CDC State is ILUPDATE
8. Data Flow
9. Mark Processed Range (Subsequent Runs)
   1. CDC State is ILEND
10. Repeat the last three steps

I am never able to get out of the ILUPDATE/ILEND 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?

Thank you so much, in advance, for your help! :)

Edit 1

Here are a couple of articles that sort of 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.

• http://www.bradleyschacht.com/understanding-the-cdc-state-value/
• http://msdn.microsoft.com/en-us/library/hh231087.aspx

The second article includes this image, which shows the ILUPDATE/ILEND loop I am trapped in.

Edit 2

Last week (May 26, 2014) I disabled then re-enabled CDC on the subscription_events 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 subscription_events 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.

Edit 3

Before I was having this problem, I was having a separate issue which I posted about here:

CDC is enabled, but cdc.dbo<table-name>_CT table is not being populated

I am unsure if these are related, but figured it couldn't hurt to provide it.

Answer 1

I had the same problem.

I have an initial load package for kickking things off and a separate incremental load package for loading updates on a schedule.

You fix it by putting a "Mark CDC start" CDC Control Task at the end of you Initial Load package only. That will leave the state value in a TFEND state, which is what you want for when your Incremental load starts.