Excel to SQL SSIS data transformation

Question

Excel to SQL SSIS data transformation

sshah 25156

Valued Member

Points: 53
More actions
March 3, 2010 at 12:08 pm

#141862

Hello,
In my SSIS package I am importing numeric data from a excel spread sheet as a string into SQL table having varchar(30) datatype.
my excel spread sheet has a value in Limit = "27457.35"
when it gets inserted into SQL table I get Limit = "27457.34999999999"
I am using DT_STR datatype in data flow task.
I have also tried converting my table column to Nvarchar and unicode DT_wstr.
That doesn't work either.
Any solution?
I don't get error executing the package but data inserted is not correct.
Thanks,
Shah

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

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

divyanth SSCarpal Tunnel Points: 4303 More actions · Answer 1

You can achieve that by changing ecel properties Like IMEX =1 and also check HDR value. please check this article..it explains you clearly

http://www.sqlservercentral.com/articles/Integration+Services+%28SSIS%29/62697/

sshah 25156 Valued Member Points: 53 More actions · Answer 2

Thank you divyanth.

For now quick fix changing a number field to text field in excel file worked but I need to have some permanent fix as user may not change the field to text field.

Thanks,

Shah

Phil Parkin SSC Guru Points: 246965 More actions · Answer 3

What is the datatype of the destination field on SQL Server?

Can you confirm that Excel is the problem by adding a data reader after the data source?

A derived column using the ROUND function could probably fix up these issues.

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

sshah 25156 Valued Member Points: 53 More actions · Answer 4

Phil,

I am migrating sql server varchar(30) datatype field.

reason behind this is we don't want to throw error at that point and migrate every file from user into dummy table. then we do validations of all data input.

I think i can try round option.

Thanks,

Shah

divyanth SSCarpal Tunnel Points: 4303 More actions · Answer 5

sshah 25156 (3/3/2010)
Thank you divyanth.
For now quick fix changing a number field to text field in excel file worked but I need to have some permanent fix as user may not change the field to text field.
Thanks,
Shah

User need not change anything.. what actually happens is when the Excel file is being read it always takes the values from first row to determine the DATATYPE.. by changing the extended properties (see the link previously posted) like IMEX = 0/1 and HDR=YEs/No .. You will be treating all the columns as TEXT fields and you can get the same values as is .. but you need to use a derived column to convert the datatypes to match your destination datatypes..

sshah 25156 Valued Member Points: 53 More actions · Answer 6

Thanks Divyanth. I am trying extended properties. Will let you know once it works.