How to manage Excel column data type with SSIS with the sampling registry key?
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. To manage Excel data type with SSIS, it is possible to change the sampling number of lines with registry key.
Change the Excel data type detection with SSIS
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.
The Excel column type detection is done by sampling data
Note that by default the number of lines sampled is 8. However, 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.
Example of classical errors when loading Excel files with SSIS
The most common error is trying to integrate an Excel file with columns that contains 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.
Error integrating an Excel column containing strings and numbers
Another classical error when you integrate Excel files is the DT_NUMERIC type instead of the DT_WSTR. For example, if the Excel column contains hundreds or thousands of lines, with numeric and characters, but the first 16 lines only contains numeric values. Then the SSIS system interprets it as a numeric column.
The SSMS tutorial below explains how to import an Excel file into a SQL Server database using SSMS with no lines of codes.