DTS Import field transformation

[vanessa03]

I am using the DTS Wizard trying to import an Access table to SQL table. It imports fine, however, I need one of the fields in Access which is left aligned text to be right aligned zero filled text. Such as 38 to be 00000038. Can I do this in the transformation part? Thanks.

 

[johnherman]

Write a ActiveX VB Script for the transform. Check the length of the incoming field (until you hit a space). Then, depending on the length of the SQL Server column, concatenate that many zeros to the front of the field. If this does not make sense, I can actually write the code for you next week when I return from Vacation (!)
Happy and Prosperus 2004.

Sometimes the grass is greener on the other side because there is more manure there - original.

 

[vanessa03]

Thanks a bunch! If you could write that for me I would really appreciate it. I'm new to SQL and VB scripting. My email address is vanessa@torminerals.com
Thanks again for your help.

 

[ClaireHCM]

You dont need VBS for the new format of the column, in the DTS package, you could specify that column format by SQL query as

SELECT right('00000000'+id,8) as id, col2, col3,...
FROM TableName

Since you specified that the column datatype is text.So here I just concatenate the prefixed zeros with column name.

If the field is in number datatype.You should change the syntax to

SELECT right('00000000'+format(id,"00000000&quot,8) as id, col2, col3,...
FROM TableName

Then the new format for the column would be accomplished.