Transforming Published Data

SQL Replication

Replication

Transforming Published Data

Transformable subscriptions (available with snapshot replication or transactional replication) leverages the data movement, transformation mapping, and filtering capabilities of Data Transformation Services (DTS). Using transformable subscriptions in your replication topology allows you to customize and send published data based on the requirements of individual Subscribers.

Examples of how you can use transformable subscriptions include:

  • Creating column and horizontal partitions of published data on a per Subscriber basis (custom data partitions).

  • Creating data transformations such as data type mappings (for example, integer to real data type), column manipulations (for example, concatenating first name and last name columns), string manipulations, and use of functions.

The option to allow transformations is set at the time you create a publication. After the option is set, and a replication DTS package is built, Subscribers to the publication can attach a DTS package and incorporate it as part of the replication data flow. This functionality is supported for Microsoft® SQL Server™ 2000 and OLE DB Subscribers (ODBC Subscribers are not supported).

You create a DTS package for replication either using the replication wizards or programmatically, such as using Microsoft Visual Basic®. You can also customize a DTS package for use with a Subscriber by using DTS Designer.

The transformable subscriptions topics assume you are familiar with DTS. For information about DTS concepts, DTS programming, and using DTS as a part of your data warehousing strategy, see DTS Basics