Solved

Convert Exponential values in SSIS

Posted on 2009-04-09
8
2,286 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
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…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.

809 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