How to create a transformable subscription (Enterprise Manager)

How to create a transformable subscription (Enterprise Manager)

1. Create a publication enabled for transformable subscriptions using the Create Publication Wizard, and on the Create Publication Wizard Welcome page, click Show Advanced Options, click Next, and then click a database in the list.
   
   
2. On the Choose Publication Type page, click either Snapshot Publication or Transactional Publication.
   
   
3. On the Updatable Subscriptions page, do not select Immediate Updating or Queued Updating because transformable subscriptions will not also be available.
   
   
4. On the Transform Published Data page, click Yes, and then continue creating the publication.
   
   
5. Build the replication DTS package using the Transform Published Data Wizard.
   1. In SQL Server Enterprise Manager, right-click the publication enabled for transformable subscriptions, and then click Properties.
      
      
   2. In the Properties dialog box for the publication, click Subscriptions, and then click Transformations.
      
      
   3. In the Transform Published Data Wizard, click Next until the Choose a Destination page is displayed. Select a provider to connect to the Subscriber (the Microsoft® SQL Server™ 2000 Replication OLE DB Provider for DTS is used only for the source connection from the package to the Distributor), and then complete the rest of the connection information. On the Define Transformations page, click the transform (...) button for a published table on which to define a transformation.
      
      
   4. On the Column Mappings and Transformations page, click the Column Mappings tab, and then click one of the following: Keep the existing table unchanged, DROP the existing table and re-create it or Delete all data in the existing table. If you want to partition data vertically, in the list, select the Destination columns you want to include.
      
      
   5. If you want to partition published data horizontally or map transformations on the published partition, click the Transformations tab for the published table. Click Transform data using the following script. Choose the type of language for the script from the drop down, edit directly in the script window or click Load file to load a script. Repeat this step for all tables in the publication for which you want to add transformations.
      
      
   6. On the Save DTS Package page, enter a Name, enter a Description, and then either enter optional password information for the package or use the supplied default values. Continue until the package is created successfully. If you want to use this package for a push subscription, save the package at the Distributor (default). If using the package for a pull subscription, save it at the Subscriber.
6. This set of steps is used to create a transformable subscription.
   1. Open either the Push Subscription Wizard or the Pull Subscription Wizard.
      
      
   2. On the Specify DTS Package page, click the DTS package you want to use with the subscription, optionally enter a package password, click Next, and then continue through the wizard until you have created the subscription successfully.