Solved

Convert Exponential values in SSIS

Posted on 2009-04-09
8
2,216 Views
Last Modified: 2013-11-10
Hi

I am trying to import datafrom a CSV File.. I have few Column hich have exponential Data (2.35E+11) something like that .. How do i convert so that i get the Numeric output i.e., 23500000000.. I have tried using Data Conversion task and convert that colum to DT_R4 single precision and DT_R8 - Double Precision but no luck..

Any Suggestions...
0
Comment
Question by:danny1620
  • 4
  • 3
8 Comments
 
LVL 15

Expert Comment

by:MNelson831
Comment Utility
Not sure how much this will help, but I just ran this SQL Statement:

select convert(decimal,'2.35E+11')

and received an error converting varchar to decimal, but when I ran it again without the quotes:

select convert(decimal,2.35E+11)

it returned 235000000000
0
 
LVL 25

Assisted Solution

by:reb73
reb73 earned 100 total points
Comment Utility
Try bringing the exponential values in as character data (DT_STR) and then convert this to a float using TSQL..
0
 

Author Comment

by:danny1620
Comment Utility
I Tried to convert the Column values to decimal as you said but i get the following error.. This is the same error when I try to convert it to DT_R4 or DT_R8
[Data Conversion [756]] Error: Data conversion failed while converting column "Column 15" (165) to column "Copy of Column 15" (772).  The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
[Data Conversion [756]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "output column "Copy of Column 15" (772)" failed because error code 0xC020907F occurred, and the error row disposition on "output column "Copy of Column 15" (772)" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure.

 
0
 
LVL 15

Accepted Solution

by:
MNelson831 earned 400 total points
Comment Utility
Using Reb's idea, I ran this successfully:

select convert(decimal,convert(float,'2.35E+11'))
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 

Author Comment

by:danny1620
Comment Utility
Thanks guys that worked ... i actually had to use  a staging table to get the data into it and then use Convert to get that into actualt data ..Now.. I have two more problems with the data i have ..
Source is a CSV but i have Few Columns were the Value has a , in it Such as South Korea,republic Of  ..Is there any way that i check that and avoid creating an extra column and the Datetime values in the source are as 2009-02-09-14.26.38.000000 instead of
2009-02-09 14.26.38.000000
So I tried using the below expression in the derived column to convert the datetime but get an error saying otential Loss of Data
SUBSTRING("[Column 16]",1,10) + " "+SUBSTRING("[Column 16]", 12,30)
Thanks
0
 
LVL 15

Expert Comment

by:MNelson831
Comment Utility
Select
     Case
         When charindex(',',[Column 16]) > o then right([Column 16], len([Column 16]) - Charindex([Column 16])) + left([Column 16],Charindex([Column 16] -1)
          else [Column16]
    End as [Column 16]
   
0
 
LVL 15

Expert Comment

by:MNelson831
Comment Utility
Oops

This was supposed to >0 not >o
 When charindex(',',[Column 16]) > o
0
 

Author Comment

by:danny1620
Comment Utility
Hi Nelson..
Thanks but the Select Statement does not work .. gives me an error saying CharIndex requires to 2 or 3 arguments ..
My expression below works
SUBSTRING([Column 16],1,10) + " " + SUBSTRING([Column 16],12,2) + REPLACE(SUBSTRING([Column 16],14,6),".",":") + SUBSTRING([Column 16],20,4)
But i want to make it a little better .. I mean dynamic .. appreciate your help
Thanks
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now