Understanding the CDC State Value

Bradley Schacht is a Principal Program Manager on the Microsoft Fabric product team based in Saint Augustine, FL. Bradley is a former consultant, trainer, and has coauthored 6 SQL Server and Power BI books, most recently Learn Microsoft Fabric. As a member of the Microsoft Fabric product team, Bradley works directly with customers to solve some of their most complex data problems and helps shape the future of Microsoft Fabric. Bradley gives back to the community through speaking at events such as the SQLBits, Fabric Community Conference, PASS Community Data Summit, SQL Saturdays, Code Camps, and user groups across the country including locally at the Jacksonville SQL Server User Group (JSSUG). He is a contributor on SQLServerCentral.com and blogs on his personal site, BradleySchacht.com.




The part of the super simple to decipher string you are wanting to look at is going to be the first part of the state before the "/CS". There are 7 distinct values that may be shown at the beginning of the state field.
After running the packages marking the processed range put the status back to ILEND. This again caused the get processing to issue a status change to ILUPDATE on the next run at which point the mark processed finally went to TFEND like I expected. In hind sight this makes sense as it is the way that CDC ensures it doesn't miss any records that were being changed during the initial load, but it was not what I was expecting to see. All subsequent runs result in a TFSTART and TFEND. Since my database source was static I could have just issued a Mark CDC start command and been on my way as shown here:
To sum up the difference in how those two situations acted here is what when on. Scenario 1 Setup: Initial Start Initial End Get Processing Mark Processed Scenario 1 Outcome: ILSTART ILEND ILUPDATE ILEND ILUPDATE TFEND TFSTART TFEND Repeat last two outcomes Scenario 2 Setup: CDC Start Get Processing Mark Processed Scenario 2 Outcome: TFEND TFSTART TFEND TFSTART TFEND Repeat outcome Hopefully this explains in somewhat useful terms what happens in each of those two situations. The best way to understand this is to set up both those demos on your own and run through them. You don't even need to create a data flow in there, you will get great benefit out of just running the different options in the control task by itself. Just be sure to only run the initial start and initial end once to start the process. Running those every time is going to be useless and simulate nothing.