SSIS Importing issue with EXCEL

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Business Intelligence » SSIS Importing issue with EXCEL

16 posts, Page 1 of 2

1 2 »»

SSIS Importing issue with EXCEL

Rate Topic

Display Mode

Topic Options

Author

Message

surendra.chatakondu

Posted Thursday, March 04, 2010 7:24 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, July 27, 2010 12:01 PM
Points: 12, Visits: 48

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

Post #876828

Ganesh Lohani

Posted Thursday, March 04, 2010 7:41 AM

SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 8:38 AM
Points: 883, Visits: 1,662

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

Post #876840

surendra.chatakondu

Posted Thursday, March 04, 2010 7:49 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, July 27, 2010 12:01 PM
Points: 12, Visits: 48

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

Post #876845

Koen Verbeeck

Posted Thursday, March 04, 2010 8:06 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 2:11 AM
Points: 9,378, Visits: 6,473

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...

How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence

Post #876856

surendra.chatakondu

Posted Thursday, March 04, 2010 8:17 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, July 27, 2010 12:01 PM
Points: 12, Visits: 48

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

Post #876867

Ganesh Lohani

Posted Thursday, March 04, 2010 10:06 AM

SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 8:38 AM
Points: 883, Visits: 1,662

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

Post #876957

avinash jobanputra

Posted Thursday, March 04, 2010 10:25 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, May 09, 2013 10:30 AM
Points: 29, Visits: 264

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

Post #876976

divyanth

Posted Thursday, March 04, 2010 11:07 AM

SSC Veteran

Group: General Forum Members
Last Login: Monday, May 20, 2013 4:16 PM
Points: 215, Visits: 551

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

Post #877020

surendra.chatakondu

Posted Thursday, March 04, 2010 12:09 PM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, July 27, 2010 12:01 PM
Points: 12, Visits: 48

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

Post #877064

avinash jobanputra

Posted Thursday, March 04, 2010 12:46 PM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, May 09, 2013 10:30 AM
Points: 29, Visits: 264

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?

Post #877102

« Prev Topic | Next Topic »

16 posts, Page 1 of 2

1 2 »»

Permissions