SSIS Scientific Notation Desirable

Question

I am working on a SSIS project that scans a directory and loops through each excel files that will then be loaded into MSSQL. Currently, I am having an issue with 2966171 being represented as 2.966171e+006. Here is what I have:

1) The Excel Connection String is passing IMEX=1; (Import Export Mode) Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\UNC\PATH\TO\Excel.xls;Extended Properties="EXCEL 8.0;HDR=NO;IMEX=1";

2) The has confirmed the data type for this field is DT_WSTR of length 255.

Things I have tried:

1) Changing the datatype in Excel to Text

2) Creating a script component that explicitly converts a string to a decimal back to a string. (Terrible Approach)

3) Casting in a derived column component.

EDIT: I must keep this column a DT_WSTR type, some other rows contain alphanumeric values.

have you tried changing the column to numeric in the components advance editor? — Jayvee
Sorry, I should have mentioned, that it must be a string because some rows in the column are alphanumeric. — txUTSA

Hadi Hadi · Accepted Answer · 2016-10-20T21:35:01

use For-each loop controls to loop over excel files and map the filepath into a variable (ex: @[User::strExcelFile])

in the foreach container you must use 2 dataflow tasks; the first one contains an excel source and a script component as a destination, the second DataFlowTask is your task

if the excel files have the same structure you must follow the steps below:

open an excel file and change the entirecolumn type to number
in the excel connection manager choose this file
in the second dataflow task in the Excel Source set the delay validation property to true
in the first dataflow task int the script component properties (script tab) put the variable "strExcelFile" in the read only variables, And in the script you must do the following steps:

First Add Microsoft.Office.Interop.Excel.dll as a reference

Second read the ExcelFile path from the variable using the following code:

Imports Microsoft.Office.Interop.Excel

Dim strExcelFiles As String = String.Empty

Public Overrides Sub PreExecute()
    MyBase.PreExecute()

    strExcelFiles = Variables.strExcelFile

End Sub

Third, in The Main Sub write create an Excel.application set the Visible Property to false Open the ExcelFile and Change the EntireColumnType to Number and save The Excel File and Close the application using the following Code:

 Dim ColIdx As Integer = 0  'Number Column index

    Dim appExcel As New Excel.Application
    appExcel.Visible = False

    Dim wrkbExcel As New Excel.Workbook
    wrkbExcel = appExcel.Workbooks.Open(strExcelFile)

    Dim wrkshExcel As Excel.Worksheet = wrkbExcel.Worksheets(0)


    wrkshExcel.Cells(1, ColIdx).EntireColumn.NumberFormat = "0"
'this will change the EntireColumn Type to Number and eliminate scientific  character E+



    wrkbExcel.Close(True)

    appExcel.Quit()

Brief, every Excel file must be edited before importing data from it because the scientific sign appear when a number is stored in a cell having a datatype different from number

SSIS Scientific Notation Desirable

1 Answers