Post reply

Inserting leading zeros - new to DTS

  • August 20, 2003 at 11:21 am

    #106443

    I was wanting to insert leading zeros when exporting a column value to an text file

  • August 21, 2003 at 10:36 am

    #470819

    1) Assuming that you have the source and destination defined and that the destination column is an 11 digit field packed with leading zeros

    2) Create the transformation

    3) Get all of your other fields done

    4) Create an ActiveX transformation for the column you want to pack with 0's

    5) Edit the transformation so that the core portion looks something like this.

    ______________________________

    Function Main()

    DTSDestination("chardate") = RIGHT("0000000000" + RTRIM(DTSSource("chardate")), 11)

    Main = DTSTransformStat_OK

    End Function

    ______________________________

    6) Save and run. I tested it and it worked just fine.

    What it does

    a) Trim the source string to make sure there are no trailing blanks/spaces.

    b) Add eleven 0's to the front of the source string - thus you could have a null string left filled all the way to eleven 0's

    c) Grab the right-most 11 characters of the line - your source column is now left flushed with 0's up to 11 characters.

    Note, in the example above,

    -- chardate is the column being transformed

    -- you should use as many 0's as the maximum length of your field and it should match the number (the last one in the line) used for the RIGHT statement. -- adjust as needed.

    Hope this helps.

    RW

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply