Transformation and leading zeros

[Bird Byte]

I have a DTS package with an Excel spreadsheet as the source and a text file
as the destination. There is a transformation that occurs on the data to
format it correctly (certain fields need quotes, others, namely date and zip
fields, do not have quotes). It works except for the zip code field. If the
first number is a zero, it gets dropped. There doesn't seem to be a way to
change the character length behind the transformation action - at least it
wouldn't let me change the default 255 length.
I am able to accomplish this when I do an import/export in Enterprise
Manager, but not in the DTS package.
If you respond, please keep in mind I am new to writing DTS packages.
Thanks much, and let me know if I need to provide more info.

[Todd C]

If you can use a standard SQL statement when pulling the data out of the
Excel file, then you can use something like this:

SELECT
RIGHT("000" + ZipCode, 5 ) AS ZipCode
FROM ...

It adds 3 leading zeros to the field, then takes the last five of the
result. I had the same issue and have seen solutions that first examine the
length and if it's 4, add a zero, and if it's 3 ... etc, but this is simple.

HTH
--
Todd C

Post by Bird Byte
I have a DTS package with an Excel spreadsheet as the source and a text file
as the destination. There is a transformation that occurs on the data to
format it correctly (certain fields need quotes, others, namely date and zip
fields, do not have quotes). It works except for the zip code field. If the
first number is a zero, it gets dropped. There doesn't seem to be a way to
change the character length behind the transformation action - at least it
wouldn't let me change the default 255 length.
I am able to accomplish this when I do an import/export in Enterprise
Manager, but not in the DTS package.
If you respond, please keep in mind I am new to writing DTS packages.
Thanks much, and let me know if I need to provide more info.

[Bird Byte]

Thanks. When I posted I didn't realize that to change a transformation
mapping you first have to delete the existing one then create a new one. I
added an ActiveX Script for that field that works:

Function Main()
DTSDestination("ZipCode") = Right("00000" +
(DTSSource("ZipCode")), 5)
Main = DTSTransformStat_OK
End Function

Thanks for the help.

Post by Todd C
If you can use a standard SQL statement when pulling the data out of the
SELECT
RIGHT("000" + ZipCode, 5 ) AS ZipCode
FROM ...
It adds 3 leading zeros to the field, then takes the last five of the
result. I had the same issue and have seen solutions that first examine the
length and if it's 4, add a zero, and if it's 3 ... etc, but this is simple.
HTH
--
Todd C

Post by Bird Byte
I have a DTS package with an Excel spreadsheet as the source and a text file
as the destination. There is a transformation that occurs on the data to
format it correctly (certain fields need quotes, others, namely date and zip
fields, do not have quotes). It works except for the zip code field. If the
first number is a zero, it gets dropped. There doesn't seem to be a way to
change the character length behind the transformation action - at least it
wouldn't let me change the default 255 length.
I am able to accomplish this when I do an import/export in Enterprise
Manager, but not in the DTS package.
If you respond, please keep in mind I am new to writing DTS packages.
Thanks much, and let me know if I need to provide more info.