The following is a sample Microsoft® ActiveX® transformation script you can use with the DTS Import or Export wizard. The script copies data in each source column to a destination column, and performs a conditional transformation on the contents of a PHONENUMBER column.
'**************************************************************
' Visual Basic Transformation Script
' Copy each source column to the
' destination column
'**************************************************************
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
'DTSDestination("PHONENUMBER") = StrNew
'************ Write Exceptions To Error Log
If (Len(StrNew) <> 10) Then
Transform = 8448 ' DTSTransformStat_ExceptionRow
Exit Function
Else
DTSDestination("PHONENUMBER") = "(" + Mid(StrNew, 1, 3) + ") " + _
Mid(StrNew, 4, 3) + "-" + Mid(StrNew, 7, 4)
End If
Transform = 1 ' DTSTransformStat_Ok
End Function