Friday 9 May 2008

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!





8 comments:

Anonymous said...

Thx. That registry trick helped me alot.

Test said...

thank you so much, you've saved me a lot of time.

Alex said...

Lately I had unpleasant problem,exactly unexpectedly all my xls files were corrupted.And I didn't know what to do.But to my surprise myself helped tool from one soft forum-advanced xlsx recovery.It is free as far as I know,moreover utility recover corrupted xlsx file with the original file.

Anonymous said...

Thanks, I had to set it to 20,000 to get it to work on a particularly problematic import in SQL2008, but I'm pleased to say that it now works.

Graham

Chris said...

This was a huge help. Thanks!

Rajesh said...

Thanks! you saved my time!

Marhault said...

This was a great answer. I had a similar problem to someone else in which I had to set it very high (ffff = 65535)

Unknown said...

Thank you very much for your help. I've been struggling to fix that issue for almost two days but the bit that did the trick was changing the data type from the source from string to text as when increasing the range of records to suggest data types it ended up suggesting string[27000] which is invalid