SSIS CDC Processing Mode Not Updating Flag Attributes
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.
SET LastName = ‘Jones’
WHERE EmployeeKey = 11
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