Scripting a Data Transformation

The DTS Import and DTS Export wizards allow you to specify how the source data is transformed when copied to the destination table. You can:

Copy source columns to destination columns without making any changes.
Use Microsoft® JScript®, Microsoft Visual Basic® Scripting Edition or Microsoft ActiveX® scripting engines to code the transformation of each source column as it is copied to the destination. DTS enumerates the list of available scripting engines automatically based on those installed on your computer.
Load a previously written VBScript or JScript script that describes how each source column is transformed as it is copied to the destination.

Use this	To do this
Copy the source columns directly to the destination columns	Copy the source columns to the destination columns without changing the data. No transformation code is executed.
Advanced	Set transformation flags, which define how data is validated and converted before it is copied from the source to the destination.
Transform information as it is copied to the destination	Specify that each source column should be transformed, based on the VBScript or JScript provided, as it is copied to the destination.
Language	Choose the scripting language used to write the transformation code. Click Java Script or VBScript.
Browse	Find and load a file containing previously written transformation code.

Examples of possible transformations include:

Modifying all character columns in the source so that they are uppercase in the destination.
Combining two source columns and inserting them into one destination column. For example, appending a surname column to a first_name column and inserting the result into a single name column in the destination.
Splitting a source column and inserting the values into multiple destination columns. For example, splitting a single date column into month, day, and year values.
Updating a running total for one or more columns.
Validating each column. Invalid rows can be skipped.

Scripting

A transformation script can perform any operation supported by the JScript, or VBScript languages. DTS exposes three collections for use with the DTS Import and DTS Export wizards:

DTSDestination
DTSSource
DTSGlobalVariables

The DTSDestination and DTSSource collections each have one property and one method.

Use this	To do this
Count property	Specify the number of columns in the object. DTSDestination.Count specifies the number of columns at the destination. DTSSource.Count specifies the number of columns at the source.
Item method	Specify the column in the object by name or ordinal (position). Used to set or get the column value. For example: DTSDestination(3) specifies the third column at the destination. DTSSource(“au_lname”) specifies the au_lname column at the source.

Use this

To do this

Count property

Specify the number of columns in the object.

DTSDestination.Count specifies the number of columns at the destination.

DTSSource.Count specifies the number of columns at the source.

Item method

Specify the column in the object by name or ordinal (position). Used to set or get the column value. For example:

DTSDestination(3) specifies the third column at the destination.

DTSSource(“au_lname”) specifies the au_lname column at the source.

The values of the source columns are copied to the DTSSource collection. The value of any source column can be used to calculate and assign a new value to the destination column. The ActiveX transformation script places the transformed value into the DTSDestination column variants, which in turn are placed by the DTSTransformScript server into the memory allocated by the DTS Data Pump for the row.

Note Only columns included in mappings are available for use in a script.

In addition to the Count property and the Item method, DTSSource and DTSDestination also support these ADO field properties:

ActualSize
Attributes
DefinedSize
Name
NumericScale
OriginalValue
Precision
Recordset
Type
UnderlyingValue
Value

The DTSLookups collection, which is used in transformations that reference lookup tables, is not available using the DTS Import and DTS Export wizards, but is available using the DTS Designer.

Note ActiveX scripts can also access and manipulate data using Active Data Objects (ADO) or invoke any object that supports OLE Automation. A script can load and execute an external program (.exe), or even a batch file (.cmd or .bat). Transformation scripts can also retrieve and execute other DTS packages.

Transformation Return Codes

Transformation return codes indicate to the calling application, for example the DTS Import Wizard, whether the transformation of the row completed successfully or if any further action needs to be taken by the calling application. The value of the transformation return code is determined by setting these constants.

Constant	Description
DTSTransformStat_OK	Transformation succeeded. Write the row to the destination if specified. Do not call any error handlers. This is the default return code.
DTSTransformStat_SkipRow	Terminate further processing of this row. Do not call any error handlers.
DTSTransformStat_SkipFetch	Do not fetch the next row. Reexecute the transformation against the current source and destination rows. No initialization is done to the destination row. This could be used to generate multiple output rows from a single input row. Subsequent transformations are still executed unless DTSTransformStat_SkipRow is also set.
DTSTransformStat_SkipInsert	Do not write the current row to the destination. Usually used to fetch the next source row to generate a simple aggregate. Subsequent transformations are still executed unless DTSTransformStat_SkipRow is set.
DTSTransformStat_Info	Success with additional information. The application may process further by reading its pvTransformUserData (if it shares that knowledge with the transformation server) or through OLE DB error records. The DTS Data Pump calls the ErrorSink if one is specified, but does not increment the error row count. Set the appropriate status bit the application requires to pass to the ErrorSink to process for more information. Use DTSTransformStat_Error to indicate error. Do not use DTSTransformStat_Info.
DTSTransformStat_OKInfo	Combination of DTSTransformStat_OK and DTSTransformStat_Info. Write row to destination if specified, and call ErrorSink with DTSTransformStat_Info.
DTSTransformStat_SkipRowInfo	Combination of DTSTransformStat_SkipRow and DTSTransformStat_Info. Abort further processing of this row, and call ErrorSink with DTSTransformStat_Info.
DTSTransformStat_Error	Error has occurred. Pass the status bit to the ErrorSink, and process as an error.
DTSTransformStat_ErrorSkipRow	Terminate further processing of this row due to an error and call the ErrorSink to handle the error. Do not write the row to an exception file.
DTSTransformStat_AbortPump	Bit-wise OR with other status bits to abort processing any further rows.
DTSTransformStat_NoMoreRows	The end of the record set has been reached.

Note Use the constant rather than the decimal or hexadecimal value of the transformation return code in the script.

The transformation return codes must be assigned to the name of the JScript or VBScript transformation function before the function exits.

This is a Visual Basic transformation script sample.

'**********************************************************************

' Visual Basic Transformation Script

' Copy each source column to the

' destination column and perform a transformation

'**********************************************************************

Function Transform()

'************ Declare Some Variables

Dim strPhone

Dim StrNew

Dim cChar

Dim iPhoneLen

Dim x

Dim y

'************ Copy Source to Destination

DTSDestination("CUSTOMERID") = DTSSource("CUSTOMERID")

DTSDestination("COMPANYNAME") = DTSSource("COMPANYNAME")

DTSDestination("CONTACTFIRSTNAME") = DTSSource("CONTACTFIRSTNAME")

DTSDestination("CONTACTLASTNAME") = DTSSource("CONTACTLASTNAME")

DTSDestination("BILLINGADDRESS") = DTSSource("BILLINGADDRESS")

DTSDestination("CITY") = DTSSource("CITY")

DTSDestination("POSTALCODE") = DTSSource("POSTALCODE")

DTSDestination("CONTACTTITLE") = DTSSource("CONTACTTITLE")

DTSDestination("FAXNUMBER") = DTSSource("FAXNUMBER")

'************ Call Any Function Or COM Object

DTSDestination("STATEORPROVINCE") = ucase(DTSSource("STATEORPROVINCE"))

DTSDestination("COUNTRY") = ucase(DTSSource("COUNTRY"))

'************ Scrub, Validate, and Calculate Missing Values

strPhone = DTSSource("PHONENUMBER")

y = Len(strPhone)

x = 1

While x <= y

cChar = Mid(strPhone, x, 1)

If (cChar >= "0" And cChar <= "9") Then

StrNew = StrNew & cChar

End If

x = x + 1

Wend

'************ Validate and Format Data

If (Len(StrNew) <> 10) Then

Transform = DTSTransformStat_Error

Exit Function

Else

DTSDestination("PHONENUMBER") = "(" + Mid(StrNew, 1, 3) + ") " + _

Mid(StrNew, 4, 3) + "-" + Mid(StrNew, 7, 4)

End If

Transform = DTSTransformStat_OK

End Function