Transformation and leading zeros

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.

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

"Bird Byte" wrote:

> 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.
>