SSIS Importing issue with EXCEL

Question

Post reply

SSIS Importing issue with EXCEL

surendra.chatakondu

SSC Enthusiast

Points: 186
More actions
March 4, 2010 at 7:24 am

#216302

My problem is, i have a discount column in excel with value of 0.74 but i load this data into a table it is loading as 0.7399. Column datatype is numeric(7,4). I tried to load 0.75 and it loaded successfully. Whenever i type 0.74 in that cell, it is loading as 0.7399. I am mad at this and i don't know what is the issue exactly. Please give your thoughts if any.
Thanks in advance.
Thanks
Suri

Viewing 15 posts - 1 through 15 (of 15 total)

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

Ganesh Lohani Hall of Fame Points: 3091 More actions · Answer 1

Use data type Numeric(7,2) or

or

you can use CAST function.

For example,

select CAST(24.4567 AS DECIMAL(4,2)) AS Price

surendra.chatakondu SSC Enthusiast Points: 186 More actions · Answer 2

I did converted into numeric in my derived column task as (DT_NUMERIC,7,4)([Third Party Discount]). Still no luck. I have to use 4 decimals, the one i showed you is only sample. Sometimes i get data with four decimals.

thanks for your comments.

Thanks

Suri

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 3

Do you type in 0.74 yourself or is the data already present in the Excel sheet.

It is possible that the actual value is 0.7933 and that Excel formats it in another way, showing it as 0.74.

You can always save your excel file as .csv and import that file. Much easier and less headaches...

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

surendra.chatakondu SSC Enthusiast Points: 186 More actions · Answer 4

I typed myself. I have already bulit-in package in production that talks to excel files only. Each excel file will have multiple tabs which is not possible in csv.

Thanks for your comments.

Suri

Ganesh Lohani Hall of Fame Points: 3091 More actions · Answer 5

The another way to solve this type of challange is to use data conversion task between source file and destination table.

Adtjob Mr or Mrs. 500 Points: 526 More actions · Answer 6

I cant seem to replicate this problem. My advise to you firstly would be to use the data viewer within SSIS and see what the value is once is leaves excel. Often it is to do with the connection manager you use to connect to Excel. Please let us know

divyanth SSCarpal Tunnel Points: 4303 More actions · Answer 7

You seem to have the similar problem.... Check this post..you will find the explanation and answer

http://www.sqlservercentral.com/Forums/Topic876267-148-1.aspx#bm876827

surendra.chatakondu SSC Enthusiast Points: 186 More actions · Answer 8

I looked at preview in dataflow task in my excel source, there it is showing correctly but when i look at the preview in sql destination task, it is showing 0.7399. Between that i am using derived column task and data conversion task. Derived column task converts that value into numeric(7,4) and data conversion task also do the same.

Thanks

Suri

Adtjob Mr or Mrs. 500 Points: 526 More actions · Answer 9

Ok it is definately something to do with the conversion. Can you confirm what the data type is of the tabel you are writing to?

surendra.chatakondu SSC Enthusiast Points: 186 More actions · Answer 10

Avinash,

Please look at the attached documents. First document shows step by step instructions how this is happening. Second attachement is the source file that i am using to import.

To answer your question, i am using numeric(7,4) datatype.

Please let me know if you need anything else.

Thanks

Suri

Adtjob Mr or Mrs. 500 Points: 526 More actions · Answer 11

Ok just looking at this now. Is there any reason you do the conversion. Sorry it might be obvious but im trying to help

Adtjob Mr or Mrs. 500 Points: 526 More actions · Answer 12

Hi,

Please take a look at my output. I may not be able to get in contact shortly. But i will endeavour to further help in the next few days if you like.

surendra.chatakondu SSC Enthusiast Points: 186 More actions · Answer 13

Attachement you sent me is in 2007 format, can you send me in .doc format if you don't mind otherwise i need to download conversion software

Suri

Adtjob Mr or Mrs. 500 Points: 526 More actions · Answer 14

Adtjob

Mr or Mrs. 500

Points: 526

March 5, 2010 at 11:19 am

#1129344

Hi,

Sure. Please find enclosed