How to Detect the Excel column data type with SSIS import into SQL Server ? The integration of Excel files via SSIS requires you to freeze the data type in the OLEDB component. This is because the Excel data type is determined in SSIS by sampling the first 8 rows of the default column in the OLEDB component for Excel sources.
Therefore, to integrate a XLSX file with the correct format you will need to ensure that the first few rows of the file are of the correct type. Or at least looks like so. For example, you can store numbers in a text column.
Change the data type detection of Excel files loaded with SSIS
Indeed, it is possible to change the number of lines sampled by Excel. More exactly the Excel Access Database Engine 12.0 driver, by changing the registry key as below.
- Open the registry editor, to do this go to the start menu, type regedit.
- Go to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel
- Change the value of the key called TypeGuessRows. for TypeGuessRows, enter values between 0 and 16 in decimal.
Typical example of problems encountered when loading Excel files with SSIS
The most common error is trying to integrate an Excel file with columns with strings longer than 255 characters. If the sampled rows do not contain any strings longer than 255 characters.
Moreover, the data type detected by SSIS in the Excel file that will be assigned to the column is: DT_WSTR(255). And not N_TEXT, and all rows longer than 255 characters will be truncated.
The link to the article below explains how to import an Excel file into a SQL Server database using SSMS with no lines of codes.