Made a few mods with my code. Inserted the sample data into a temp table, zeroed out the ConsecutiveD column, modified my code to use the temp table and changed it to an update. Looks like it works and updates the underlying temp table.

I will be honest, the best way to do what you want is with SQL. If you want to do this in SSIS, do it using an Execute SQL Task and have it run the SQL code for you there.