CODEWITHSUNDEEP
sql-serverssisloadingxls
Susmita Patil
Susmita Patil

Reputation: 1

Load data from Excel to SQL Server using SSIS

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.

Upvotes: 0

Views: 74

Answers (1)

Amira Bedhiafi
Amira Bedhiafi

Reputation: 1

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.

enter image description here

If you enable the data viewer you will understand how it works : enter image description here

In your case you can use a data conversion component

Upvotes: 1

Related Questions