SSIS CDC Processing Mode Not Updating Flag Attributes

Posted on June 6, 2012. Filed under: CDC, SSIS | Tags: , |

When using the CDC Source adapter in SQL Server 2012 you are allowed to specify a number of options as to how you want your changed data to appear (Processing Mode).  I am not going to list all the options as I only want to bring our attention to one of them Net With Update Mask.

This processing mode is described as follows

This processing mode is similar to Net, except that there will be an additional boolean column (__$<column-name>__Changed) that indicates whether a column value changed. This may be a useful processing mode if you are only monitoring/processing changes to certain fields, or wish to provide additional processing logic when changes are made to a specific field.

If you want to learn more about the other processing modes then head over to Matt’s article

This sounded like a fantastic solution for something I wanted to use but I hit upon an issue.

After issuing an UPDATE statement against my source table and only altering a subset of the available columns I found that all the  __$<column-name>__Changed attributes had a value of false.  Not what I was expecting.

UPDATE EmployeeSource
SET LastName = ‘Jones’
WHERE EmployeeKey = 11

LastNameChanged

I spoke with Matt Masson and he tells me that when he looked at it pre RTM he could only ever get the attributes to say true

I have put this on Connect

Liked it here?
Why not try sites on the blogroll...

Follow

Get every new post delivered to your Inbox.

Join 201 other followers