How Transforming Published Data Works

When a publication is configured to allow DTS transformations, the Subscriber is allowed to specify a DTS package as part of setting up a subscription. The following diagram illustrates how snapshots and subsequent incremental changes are transformed before the data is applied to the Subscriber.

Snapshots

During the process of applying the snapshot, the Distribution Agent loads the replication DTS package from the msdb database (or loads a saved .dts file, in the case of OLE DB pull Subscribers). The SQL Server replication OLE DB Provider for DTS converts snapshot data into an OLE DB rowset that is used to drive a DTS Data Driven Query task, which performs any specified transformations or filtering operations before applying the data to the Subscriber. This is a special purpose OLE DB provider intended for use only by replication and not a general purpose OLE DB provider.

The following events and processes occur when a DTS package is included in the replication data flow:

• A DTS package is created with the snapshot .bcp (bulk copy) file as the source input to the package.
  
  
• The Subscriber table is created from the script in the DTS package Execute SQL task.
  
  
• The Data Driven Query task, used in a replication DTS package, moves data using Transact-SQL INSERT statements. When snapshots are applied or reinitialized, the equivalent of an INSERT statement for each row of data is executed by the DTS package.

For publications allowing DTS transformations, the snapshot .bcp data files are generated as character-mode because native format .bcp files cannot be used with DTS.

Heterogeneous Subscribers can subscribe to publications for which the snapshot is created in character-mode, as long as the publication allows transformations of published data.

Incremental Changes

As incremental changes occur at the Publisher, the Distribution Agent retrieves transactions that need to be replicated from the distribution database, and processes them in the same way described for applying a snapshot. In this case, however, the data source is the MSrepl_commands table rather than a .bcp character-mode data file. For incremental changes, the Data Driven Query task handles UPDATES and DELETES in addition to INSERTS, and applies the incremental changes for individual statements within a transaction according to the type of incremental change and its specified transaction mappings (for example, if the change is mapped to a transaction with INSERT, UPDATE or DELETE statements).

Note  When columns are added to or dropped from a publication that allows transformations on published data, the DTS packages will need to be regenerated.