ActiveX Script Transformations

You can use a Microsoft® ActiveX® script to code a transformation between one or more source and destination columns, or between a source or destination column and a global variable.

Because transformation scripts are executed for each row of data transformed, actions such as creating ADO connections, while useful in ActiveX script tasks, are not appropriate here. These are examples of ActiveX script transformation scripts.

Simple Column Transformation

This ActiveX transformation script, written in Microsoft JScript®, copies all the columns from the source-to-destination table, converting any text characters to upper case:

function main() {

        for ( var i = 1; i <= DTSSource.Count ; i++ )
                DTSDestination(i) = toUpperCase(DTSSource(i));

  return(DTSTransformStat_OK);


}


Concatenating Columns

This ActiveX transformation script, written in Microsoft Visual Basic® Scripting Edition, in the Employees table of the Northwind database:

• Copies the Employee ID and Title columns from the source table to the destination table without making any changes.
• Executes a transformation that consolidates the Last Name and First Name columns in the source table into a single Name column in the destination table, and converts the data to uppercase:

  function main()

        DTSDestination("Employee ID") = DTSSource("Employee ID")
        DTSDestination("Name") = UCASE(DTSSource("Last Name") +" " _
                + DTSSource("First Name")
        DTSDestination("Title") = DTSSource("Title")

        Main = DTSTransformStat_OK

end function


Transforming Date Data

When importing data from a file to an OLE DB destination table, you may need to use the CDATE function to convert date data if the date format is in a text or character field and is not in the format required by OLE DB, which is yyyy-mm-dd hh:mm:ss:sss:

function main()

        DTSDestination("Total Sales") = DTSSource("Total Sales ")
        DTSDestination("DestColumnDate") = CDATE(DTSSource("SourceColumnDate"))

        Main = DTSTransformStat_OK

end function


See Also

Global Variables