Hi, I have a requirement with Replicate that I'm not sure it can handle. I need to set up two tasks that will replicate several tables from two source databases into a single target database. However, I need to know which source database the data has come from so I was going to use a Global Transformation to create a new column called 'Source' which would be used to identify the source database. The problem is that I also need this new column to be part of the primary key in the target table but this functionality is not supported by a global transformation so I was wondering if there is a recommended way to achieve this? I was thinking I could drop and recreate the primary key on the target tables after the initial load was complete but not sure if this would then mess with the replication if the source tables have a different primary key to the target tables.
@RichSmart - Replicate will use the PK as defined in the target when creating the statements to update the target.
(Obviously test this in a non-prod env first 🙂 ). You should configure the task to "Do Nothing" on FULL reload - as you will need to manually perform a delete where source='source to reload'.
Configure the Replicate task as you've mentioned with a global transform and alter the PK's on the target to include the new Column. Replicate will follow the target PK definition as an override to the source PK definition.
As I said - test this first in a non-production environment. Tracing the target env will show the sql statements or you can turn verbose logging on for TARGET APPLY to see the stmt used by Replicate. (Just don't forget to turn logging back to normal levels after your test).
@RichSmart - Replicate will use the PK as defined in the target when creating the statements to update the target.
(Obviously test this in a non-prod env first 🙂 ). You should configure the task to "Do Nothing" on FULL reload - as you will need to manually perform a delete where source='source to reload'.
Configure the Replicate task as you've mentioned with a global transform and alter the PK's on the target to include the new Column. Replicate will follow the target PK definition as an override to the source PK definition.
As I said - test this first in a non-production environment. Tracing the target env will show the sql statements or you can turn verbose logging on for TARGET APPLY to see the stmt used by Replicate. (Just don't forget to turn logging back to normal levels after your test).
