Solved

SSIS - Importing scientific notation field to SQL DB using SSIS

Posted on 2014-07-24
3
446 Views
Last Modified: 2016-02-11
Hi

I am trying to import a column from an excel source. It has a "general" type in excel and therefore shows as follows: 8.938E+19

I want to import it into SQL by showing it as its numeric value of:  89380000000004700000

I am attempting to use a derived column and would like to know what I should convert it to in order to import it into the DB.
0
Comment
Question by:devguru001
  • 2
3 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 40217479
Since we're talking 20 digits, and bigint only supports 19, you'll have to use numeric.
-- This works
SELECT CAST(89380000000004700000 as numeric(30,0)) 

Open in new window

btw if this value is not going to be searched much, or used in an expression, you can consider a char data type.
0
 

Author Comment

by:devguru001
ID: 40218706
Hi Jim,

I defined the table column as nvarchar100.
I have a derived column in my ssis package: (DT_NUMERIC,30,0)[fieldtocast].
It still shows in scientific notation after running the package. Any thoughts?
0
 

Author Comment

by:devguru001
ID: 40223588
My source data supplier was willing to change the column from "General" to "Number".
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

My client has a dictionary table. They're defining a list of standard naming convention. Now, they are requiring my team to provide us a mechanism how to match new incoming data with existing data in their system.
A couple of weeks ago, my client requested me to implement a SSIS package that allows them to download their files from a FTP server and archives them. Microsoft SSIS is the powerful tool which allows us to proceed multiple files at same time even w…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.

770 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