Create Sql server replication with some data transformation (during or at the end)

Question

Hi,

i'm running a sql server replication to sync a table to a separate (reporting) database.

In the source database, all records have a DateTime value (not so unusual) but it isn't stored as a DateTime, but as a Guid... which points to a table which contains all timestamps (one for every minute).

Now what i would like to do is during the replication process (or at the end, or continuous at the target database) is lookup the timestamp (in datetime value) in the TimeStamps database and fill an extra field in the target database with that DateTime value.

The reason for that is that i can query the target database on specific datetimes without having to join it to the timestamps database.

Is this possible?

I've seen as a property of the replication a 'DTS' property, which makes me think i can somehow specify a DTS package, but the property isn't enabled and i thought DTS is deprecated?

Answer 1

Assuming a transactional replication, I would customize the sp_MSins_YourTable and sp_MSupd_YourTable stored procedures on the subscriber to do the extra lookup/translation. Just be sure to fully document this customization as any time you would have to drop/recreate replication your custom procs will be replaced with the standard ones again.