Manage Excel data type with SSIS before integration

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.

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.

SSIS detects the type of Excel columns by sampling

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 one of these registry keys, it depends on your installation and the driver used to read the Excel files:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\16.0\Access Connectivity Engine\Engines\Excel

Then change the value of the key called TypeGuessRows, enter values between 0 and 16 in decimal.

Excel file integration errors 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 a mixed Excel column with text 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.