Solved

SSIS - Importing scientific notation field to SQL DB using SSIS

Posted on 2014-07-24
3
428 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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Here's a requirements document template for an integration project (also known as Extract-Transform-Load or ETL) based on my development experience as an SQL Server Information Services (SSIS) developer over the years.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.

744 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

8 Experts available now in Live!

Get 1:1 Help Now