Solved

SSIS - Importing scientific notation field to SQL DB using SSIS

Posted on 2014-07-24
3
487 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 66

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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

717 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