Change Capture stage vs Difference stage driving me nuts
Posted: Tue Aug 02, 2011 1:14 am
I'm trying to compare a set of potential updates with existing data on a table. The updates have a natural key which is also on the table but is not the primary key (it has a surrogate key).
the catch is that I also want to bring the primary (surrogate) key through for changed records so I can use it to apply updates.
I have done extensive searching of this forum, without a solution.
The documentation and behavior of the Difference Stage seems to indicate that it can also bring through non-key and non-value columns whereas the Change Capture stage only allows columns that appear on both inputs to be used at the output.
The doco for the difference stage says:
Can I get additional columns to the output using the Change Capture stage?
the catch is that I also want to bring the primary (surrogate) key through for changed records so I can use it to apply updates.
I have done extensive searching of this forum, without a solution.
The documentation and behavior of the Difference Stage seems to indicate that it can also bring through non-key and non-value columns whereas the Change Capture stage only allows columns that appear on both inputs to be used at the output.
The doco for the difference stage says:
So, it is saying thatIf the before and after data have the same column names, then one data set effectively overwrites the other data set and so you only see one set of columns in the output. Which data set is output is controlled by the settings on the Link Order tab and the Mapping tab. If your before and after data sets have different column names, columns from both data sets are available to be output as set on the Mapping tab. Any columns that are designated as key or value columns in the input data sets must have the same names.
- columns whose names don't match will be available on output
- columns whose names do match will have the value available at output based on the input ordering AND you will get a warning
- "value" columns (used for comparing) MUST have matching names
Can I get additional columns to the output using the Change Capture stage?