Solved

Convert Exponential values in SSIS

Posted on 2009-04-09
8
2,311 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
ID: 24109557
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
ID: 24109631
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
ID: 24109663
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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 15

Accepted Solution

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

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

Author Comment

by:danny1620
ID: 24111487
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
ID: 24111542
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
ID: 24111552
Oops

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

Author Comment

by:danny1620
ID: 24112036
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

837 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