Change Capture stage warning

srinivas.nettalam · Post by **srinivas.nettalam** » Mon Sep 26, 2011 11:37 pm

Hi All,
I am getting the warning below and warnings are not allowed in our project.I am using the column mentioned in the warning in both "after" and "before" datasets but excluded that from being a value column and it is present in the output column for the updates on the tables.
RCP is disabled on the project level.

One solution I have is to have a copy stage before CDC stage and drop that column and rejoin.
Please suggest if there is another way....

Warning:
CDC_Knid: When checking operator: Defaulting "InsertType" in transfer from "beforeRec" to "outputRec".

meet_deb85 · Post by **meet_deb85** » Mon Sep 26, 2011 11:39 pm

Does your project allows you to use message handlers?

srinivas.nettalam · Post by **srinivas.nettalam** » Mon Sep 26, 2011 11:48 pm

Yes,If no otherway is found.

meet_deb85 · Post by **meet_deb85** » Mon Sep 26, 2011 11:51 pm

Then its better to use a message handler rather than going for a join

srinivas.nettalam · Post by **srinivas.nettalam** » Mon Sep 26, 2011 11:57 pm

Is that the only way?I couldn't understand the reason behind this warning.

meet_deb85 · Post by **meet_deb85** » Tue Sep 27, 2011 12:05 am

Have you tried using Explicit Keys and Values?

srinivas.nettalam · Post by **srinivas.nettalam** » Tue Sep 27, 2011 12:12 am

yes still the warning were there.

SURA · Post by **SURA** » Tue Sep 27, 2011 12:14 am

It will until you change the design, but that is more costly.

Rather than that, you can pass only the colums you need to check for Key and change values.

DS User

ray.wurlod · Post by **ray.wurlod** » Tue Sep 27, 2011 1:22 am

Is the InsertRec column explicitly mapped from input to output in the stage?

srinivas.nettalam · Post by **srinivas.nettalam** » Tue Sep 27, 2011 2:02 am

Yes Ray.
I am mapping that column to the output link since I have to update the records in the target table with the value coming from after dataset.Only change is not captured based on that value.Is there anyway to eliminate those warning through someoption in CDC stage itself?

PhilHibbs · Post by **PhilHibbs** » Thu Oct 27, 2011 8:43 am

I am having exactly this same issue. I have a set of updates to perform on about 40 columns, but I only want to do Change Capture on 5 of them. All of the other 35 or so columns that I am passing through on the "After" link (which is the update data) but not doing Change Capture on show in the job log as warnings. I also have Explicit Keys & Values set. It makes no difference whether I have these columns coming in on the Before link or not.

Looks like we will have to do a Message Handler.

BI-RMA · Post by **BI-RMA** » Thu Oct 27, 2011 9:18 am

CDC needs identical input-columns on both links. If a column is missing on one link you will get the quoted warning, even if you are not using the column.

Add the column on the before-input-link (set to Null if necessary). Obviously, use explicit values, since you do not want to compare this column to the after record.

PhilHibbs · Post by **PhilHibbs** » Thu Oct 27, 2011 9:21 am

BI-RMA wrote:CDC needs identical input-columns on both links. If a column is missing on one link you will get the quoted warning, even if you are not using the column.

Add the column on the before-input-link (set to Null if necessary). Obviously, use explicit values, since you do not want to compare this column to the after record.

It makes no difference whether I have these columns coming in on the Before link or not.

fmou · Post by **fmou** » Sat Oct 29, 2011 12:10 pm

I thought only the key columns and the value columns can pass the CDC. For all the rest of the columns, ie. the don't-care ones, they can't pass through the CDC. Correct?

thanks

BI-RMA · Post by **BI-RMA** » Mon Oct 31, 2011 8:58 am

fmou wrote:I thought only the key columns and the value columns can pass the CDC. For all the rest of the columns, ie. the don't-care ones, they can't pass through the CDC. Correct?

thanks

Double checked this. They can if they are on the after-input-link, but the mentioned warning will be issued in this case.

To avoid the warning it would be necessary to send the extra-columns around the CDC in another stream to rejoin them behind it. I would personally use message-handlers only as a last-resort solution, because not all of the messages are related to a unique scenario. Demoting the (obsolete) message for one scenario may result in a different (potentially problematic) message demoted after a change in the job.