ActiveX Transformation Script

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

 

  


(c) 1988-98 Microsoft Corporation. All Rights Reserved.