Sql Server 2005 Excel Import Error

Today, the first day of the year,
unfortunately i am working on a project which needs to import a large amount of data from excel into an Sql Server 2005 database.

I selected the Sheet i wanted to import ...






















And these were the table's mappings ...



Notice the Desc field which displays 255 size.

After that when i tried to finish importing data i got the following errors:



Error 0xc020901c: Data Flow Task: There was an error with output column "Desc" (21) on output "Excel Source Output" (9). The column status returned was: "Text was truncated or one or more characters had no match in the target code page.". (SQL Server Import and Export Wizard)

Error 0xc020902a: Data Flow Task: The "output column "Desc" (21)" failed because truncation occurred, and the truncation row disposition on "output column "Desc" (21)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component. (SQL Server Import and Export Wizard)

Error 0xc0047038: Data Flow Task: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Source - Sheet1$" (1) returned error code 0xC020902A. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure. (SQL Server Import and Export Wizard)

Error 0xc0047021: Data Flow Task: SSIS Error Code DTS_E_THREADFAILED. Thread "SourceThread0" has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited. (SQL Server Import and Export Wizard)

Error 0xc0047039: Data Flow Task: SSIS Error Code DTS_E_THREADCANCELLED. Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. There may be error messages posted before this with more information on why the thread was cancelled. (SQL Server Import and Export Wizard)

Error 0xc0047021: Data Flow Task: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0047039. There may be error messages posted before this with more information on why the thread has exited. (SQL Server Import and Export Wizard)

Trying to figure out the problem i found this were Microsoft actually says that when importing data from Excel into Sql Server it checks by default only the first 8 rows of the Excel Sheet.
To change this you have to dig into registry and change the TypeGuessRows key in the path HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel,
as follows ...



I have changed this value from 8 to 0, which means 16384 rows to scan (if available).

Be aware, you should notice this:

"The valid range of values for the TypeGuessRows key is 0 to 16. However, if the value is 0, the number of source rows scanned is 16384. Keep in mind that a value of zero may cause a small performance hit if the source file is very large."

After these changes the mappings now for the over sized column "Desc" have changed to max as seen here ...


After that the data import worked just fine.
Performance was indeed much worse as Microsoft mentioned, but this is not such a problem.