SSIS : Decimal Places not loading from Excel Source to SQL table

Question

Hi Team - I have an excel file with few columns but the issue is with one of the column, Lets say Column A.

Column A :
FY20

Work

Extra

Feb

4

4,401,0089

3,81,68182.01

27,381.59

-

42590062.77

99.8%

Few of the numeric values above are rounding off in excel itself but the actual value is when i click on the cell (27,381.58999999).

So, If you have seen above Column A has different types of values in each of its cells ( like numeric with decimals, hyphens, percentages and string values and values with decimals rounding off).

So i want to load the data from excel as is without rounding off and it should load the complete decimal value of the numeric value.

The destination column is nvarchar but i can change that too.

Can you please let me know what are the options? I tried data conversions to DT STR and DT_DECIMAL ( but this doesn't work as there string characters too).

I also clicked on the cells of each of these values, some are showing as 'custom', few are showing as 'General'.

I am looking for your suggestions on how these values can be imported from excel source without any data loss.

Note: The source format cannot be changed by user.

Thanks

Answer 1

rounding off in excel itself but the actual value ... as different types of values in each of its cells

Right, Excel only know a value and a format information.

If a cell has value = 27,381.58999999 and format = #,##.00 the Excel shows a rounded numeric.

SSIS imports the value as it is without modifying/formatting it and that's what you get.

Answer 2

Hi @kkran，

Like Olaf said, when you load the file in SSIS data flow, it will show the actual value you want.