Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SSIS Importing issue with EXCEL Expand / Collapse
Author
Message
Posted Thursday, March 4, 2010 7:24 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Thursday, March 4, 2010 7:41 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 2:28 PM
Points: 888, Visits: 1,731
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
Posted Thursday, March 4, 2010 7:49 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Thursday, March 4, 2010 8:06 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Sunday, July 20, 2014 11:20 PM
Points: 13,252, Visits: 10,133
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
Posted Thursday, March 4, 2010 8:17 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Thursday, March 4, 2010 10:06 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 2:28 PM
Points: 888, Visits: 1,731
The another way to solve this type of challange is to use data conversion task between source file and destination table.

Post #876957
Posted Thursday, March 4, 2010 10:25 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 3:32 AM
Points: 43, Visits: 322
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
Posted Thursday, March 4, 2010 11:07 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, October 17, 2013 4:51 PM
Points: 215, Visits: 556
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
Posted Thursday, March 4, 2010 12:09 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Thursday, March 4, 2010 12:46 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 3:32 AM
Points: 43, Visits: 322
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 »

Add to briefcase 12»»

Permissions Expand / Collapse