The only way I found to resolve the problem is to move your rows around in the Excel sheet so that the first 5 / 10 rows have an even (or close to even) number of different values for that column. So, the first 2 rows would be decimal, the next two would be character. This way, SSIS defaults to the character as the best way of picking up all values.
I have tried (in SSIS) to change all the datatypes around, but I still only ever got nulls until I did the above. Excel is horrible to insert from. If this is going to be a constant, automated process, I’d get the people providing the Excel sheet to instead provide a delimited CSV file. At least with a CSV file, you can change the datatypes in SSIS and still get the values you’re looking for.
src - https://www.sqlservercentral.com/forums/topic/excel-source-to-import-data-gets-some-values-as-null
–
copy rows with headers from excel and paste it to http://www.convertcsv.com/csv-to-sql.htm or https://codebeautify.org/csv-to-sql-converter
create at glance the insert queries\1
origin - https://www.pipiscrew.com/2019/04/ssms-import-excel-file-gets-some-values-as-null/ ssms-import-excel-file-gets-some-values-as-null