Search code examples
sql-serverexcelssisbusiness-intelligence

Scientific Notation Issue while loading data from Excel (xlsx) file to SQL Tables via SSIS

I'm loading data from excel file (.xlsx) to SQL table using SSIS package. For one column it's adding scientific notations in the data, it's already there in the excel file. But it's actual value is not loading to SQL table. I tried multiple option of derived columns, expressions etc. But I couldn't get the proper value.

This column has data of numeric and nvarchar values. Below is the example of the column.

ApplicationNumber  
1.43E+15  
923576663  
25388447  
TXY020732087  
18794588  
TXAP0000140343  

**Actual Values -**  
ApplicationNumber  
1425600000000000  
923576663  
25388447  
TXY020732087  
18794588  
TXAP0000140343

There is no issue with data coming from Business to Excel. But how we can handle this scenario in SSIS ? I also tried (DT_I8)ApplicationNumber==(DT_I8)ApplicationNumber, But it giving values for the above 1.43E+15 -> 1.430000000000000 and not the 1425600000000000

Solution

  • One thing you can do is set the output in advanced editor of the excel source as decimal with a large scale, 20 digits for example:

    enter image description here

    UPDATE

    to consider also strings in the same column you may need to redirect the error output as these will throw a conversion error:

    enter image description here

    enter image description here

    in advanced editor:

    enter image description here

    Default output:

    enter image description here

    Error output:

    enter image description here

    Then you can update your database from both the default and the error output.