Leading zeros are gone when migrating data from excel using SSIS

Question

Leading zeros are gone when migrating data from excel using SSIS

Aspg

Ten Centuries

Points: 1363
More actions
July 14, 2010 at 8:52 am

#142035

Hi All,
I am new to the SSIS . I am migrating data from .xls to SQL Server db. I have a primary key column which is of int datatype . Now the data coming from excel is having entries like '0001','0002'. After migrating data using Data conversion transformation i am getting the column values as 1,2,3... The leading zeros are gone after migration . I am a bit confused with type casting in derived column transformations. Can anybody help me to solve this problem .I am stuck with this...
Aditi:-)

Viewing 6 posts - 1 through 5 (of 5 total)

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

Cadavre SSC-Forever Points: 41690 More actions · Answer 1

INTs don't have 0's padding. If you want to keep them, you'll have to use something else, e.g. VARCHAR.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

divyanth SSCarpal Tunnel Points: 4303 More actions · Answer 2

That's true.. you need to convert it to varchar to get the leading 0's.. You might not see the leading 0's even if you change it to varchar, because Excel interprets the datatype based on the first one/two values in the column .. you might need to change the properties of the Excel Connection Manager

Aspg Ten Centuries Points: 1363 More actions · Answer 3

Yes , i changed the properties of excel connection manager but still can't load the leading zeros..

Phil Parkin SSC Guru Points: 246979 More actions · Answer 4

skcadavre (7/14/2010)
INTs don't have 0's padding. If you want to keep them, you'll have to use something else, e.g. VARCHAR.

I'd leave it as int and add a char(4) derived column which puts the zeros back ...

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

divyanth SSCarpal Tunnel Points: 4303 More actions · Answer 5

itsaditi2001 (7/14/2010)
Yes , i changed the properties of excel connection manager but still can't load the leading zeros..

Did you Change the IMEX Value to 1 ? If the Number of leading 0's is always the same then you can just bring them as integers and add them later using a derived column like Phill suggests