ActiveX Transformations

Earlier, I mentioned that DTS provides several mechanisms for employing ActiveX script code to enhance package functionality. One of the ways you can use ActiveX code to enhance the functionality of a DTS package is through ActiveX transformations. ActiveX transformations give you complete control over the mapping of source columns to destination columns in a data transformation. You can concatenate multiple source columns into a single destination column, and you can break a single source column into multiple destination columns. You can assign a destination column using no source columns at all (usually the source data comes from global variables or lookups in this scenario); you can transform a source column without using a destination column (usually a global variable is the target of the transformation in this case); and you can perform transformations where you have neither source nor destination columns (e.g., to manipulate global variables or external objects via the FileSystemObject or ADO).

This is best understood by way of example. The sample ActiveX script in Listing 20.3 combines the address, city, state, and zip columns from the pubs..authors table into a single destination column.

Listing 20.3.

Function Main()
  DTSDestination("address") = DTSSource("address") & " " _
    & DTSSource("city") _
    & ", "+DTSSource("state") & " " &DTSSource("zip")
  Main = DTSTransformStat_OK
End Function

Note the use of the built-in DTSSource and DTSDestination objects. Since an ActiveX transformation script is executed for each row in the source data, DTSSource will always refer to whatever the current row in the source is, and DTSDestination will always refer to the current row in the destination. You can find the source to this package in the file ActiveXTransformationExample.DTS in the CH20 folder on the CD accompanying this book.