I’m trying to load data from Excel to table using SSIS but it’s not getting loaded properly. Example: my column1 contains data as below:
| Column 1 |
|---|
| 1 |
| 1.1 |
| 1.1.1 |
| 1.2 |
| 1.3 |
| 1.4 |
Output coming in sql server as below;
| Column 1 |
|---|
| 1 |
| 1.10000000000001 |
| Null |
| 1.2 |
| 1.3 |
| 1.39999999999999 |
Expected output should be same as source. I have tried this with string data type. But still not working.
SSIS implements its data types while taking data from various sources, operating it, and exporting it to one of many destinations.
If you use data that contains mixed data types, by default, the Excel driver reads the first 8 rows (configured by the TypeGuessRows register key).
Based on the first 8 rows of data, the Excel driver tries to guess the data type of each column.
In your case,if your Excel data source has numbers (float) and text in one column, if the first 8 rows contain numbers (float), the driver might determine based on those first 8 rows that the data in the column is the float type. In this case, SSIS skips text values and imports them as NULL into the destination.
If you enable the data viewer you will understand how it works :
In your case you can use a data conversion component
float. Is the file really an xls file too, rather than an xlsx?