SSIS - Importing scientific notation field to SQL DB using SSIS

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.
devguru001Asked:
Who is Participating?
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
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
 
devguru001Author Commented:
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
 
devguru001Author Commented:
My source data supplier was willing to change the column from "General" to "Number".
0
All Courses

From novice to tech pro — start learning today.