Tuesday 2 July 2013

SSIS - Truncation error while exporting from excel to database

SSIS - Truncation error while exporting from excel to database 

 

The reason it fails is because it considers the excel sheet to be until 255 characters in length.

The engine that is responsible for importing excel ONLY SCANS THE FIRST 8 ROWS of the sheet .

If your first 8 rows have less than 255 characters and you have other rows more than 255 characters, then you are in trouble.
The solution is actually changing the registry of server that hosts the SQL. 
Change TypeGuessRows value to 0.

This will scan the entire excel sheet to determine the best data size & datatype for your excel sheet.
For 32 bit OS
HKEY_LOCAL_MACHINE\SOFTWARE\Mi­crosoft\Jet\4.0\Engines\Excel\TypeGuessRows

For 64 bit OS
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Mi­crosoft\Jet\4.0\Engines\Excel\TypeGuessRows

 

No comments:

Post a Comment