SSIS Excel Data Source Error - “Text was truncated or one or more characters had no match in the target code page.”

I also hit this error at my previous company http://www.ssw.com.au/ when I was doing migration from Excel spreadsheets via SQL Server Integration Services (SSIS) for my client Queensland Water Infrastructure.

As described by my http://www.oakton.com.au/ colleague Craig at http://waxtadpole.wordpress.com/2008/04/28/hello-world/, this issue also came up at our current client "LL". The issue is that the sample taken by the JET driver to determine the input datatype on the Excel spreadsheet is just not large enough. e.g. if you have 100 characters for "Column A" on every row except for line 2000, then it will not be picked up and the data type will be set to Unicode string [DT_WSTR] 255 - it will then fail with a truncation error. Even if you override it in the Advanced tab, SSIS will keep setting the size back to the value of 255. The fix is to either:

1. Put a large placeholder on the first line of the spreadsheet to force the detection of a larger size field (so it gets promoted to a "Unicode text stream [DT_NTEXT]")



2. Increase the sample size so that it picks up the correct field size (Recommended). You can increase the number of rows sampled by increasing the value of TypeGuessRows under the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel registry key. By default, this is the ludicrously small value of 8(!) lines. For a more reasonable 100 lines, you can copy the following into a .reg file and double click to update your registry:

Windows Registry Editor Version 5.00[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel]"TypeGuessRows"=dword:00000064

After you do this, it will suddenly realize that the column settings are too small and update the data type to "Unicode text stream [DT_NTEXT]".

Also note that you should close your spreadsheet before opening your SSIS package - otherwise it will wait indefinitely with "Visual Studio is Busy" messages!