Using Transformable Subscriptions with Data Transformations

Using Transformable Subscriptions with Data Transformations

You can use transformable subscriptions for basic data transformations and column manipulations between a Publisher and Subscriber, including:

• Changing data types (except for primary key columns).
  
  
• Renaming a column.
  
  
• Concatenating columns.
  
  
• Extracting a substring from, or adding characters to, a column.
  
  
• Applying functions to column values.

You map transformations in the Transform Published Data Wizard, on the Column Mappings and Transformations page, on the Transformations tab, using Microsoft® ActiveX® scripts written to the DTS object model (the same as when defining partitions). Using scripting code, specify the transformation in the ActiveX script edit box. For example, to concatenate the first name and last name columns of published data to a one name column in the Subscriber, you could use the following Microsoft Visual Basic® Scripting Edition code (the second line of the function shows the column concatenation):

Function Main()
   DTSDestination("CustID") = DTSSource("CustID")
   DTSDestination("Name") = DTSSource("LName") & " " & DTSSource("FName")
   DTSDestination("Item") = DTSSource("Item")
   Main = DTSTransformStat_OK
End Function