July 14, 2010 at 8:52 am
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:-)
July 14, 2010 at 8:56 am
INTs don't have 0's padding. If you want to keep them, you'll have to use something else, e.g. VARCHAR.
July 14, 2010 at 9:01 am
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
July 14, 2010 at 9:11 am
Yes , i changed the properties of excel connection manager but still can't load the leading zeros..
July 14, 2010 at 9:13 am
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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 14, 2010 at 9:33 am
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply