SSIS, Import and Export Wizard Issue: "Text was truncated". Using TypeGuessRows to Solve.

Hey everyone, Geon back from the dead with another SQL Something!

Today we take a peek at a very, *very* annoying and very old issue with importing data into a SQL Database. This problem occurs when using either the Import/Export Wizard or SSIS.

The issue can take a couple forms:

• "Text was truncated or one or more characters had no match in the target code page."
• "A truncation error occurred on the specified object of the specified component."
• Etc.

The keywords here are 'truncated' or 'truncation'.

Now you may think "Well, obviously it's probably your column lengths in the destination, so just increase that.".

Well, what if your destination columns are maxed out? What if it wasn't the destination?

I'd believe you to be honest...

Yes, this time the issue is because of SSIS. Or more accurately, how SSIS looks at the source file to determine appropriate datatypes and, FOR SOME REASON, the column lengths.

SSIS looks at the top 8 rows of the source data from an Excel document to make an educated guess on datatype information. And it certainly is a guess.

If your max column length is not within those 8 rows you will get the previous errors if you have a varchar/nvarchar datatype of more than 255 characters. It does not matter what max character value your destination is set as.

One way to solve this is by going in the registry (unfortunately). We want to change the 'TypeGuessRows' value. This value controls the amount of rows Excel uses for it's preview and guesswork. The value can be set between 0 - 16 where '0' in this case means it will look at all rows for preview. NOTE: If you have a lot of rows this can SLOW DOWN performance.

So let's set this up.

Use the following steps:

• Open 'Regedit'
• Use the following path: HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Office\16.0\Access Connectivity Engine\Engines\Excel
• Please note depending on your Excel version, the '\16.0\' portion may change. Please refer to this article for details.
• Right click 'TypeGuessRows', click 'Modify'.
• Change "Value Data" from '8' to '0'. (Fig. 1)
• Close Regedit.
• May need to restart the machine, or at least the Import/Export Wizard for it to take effect.
• When you're done with your import(s), you may wish to return the value to it's default of '8'.

Fig. 1: Changing Registry values. Sigh.

 
And that should hopefully solve your issue. :-)


Now honestly, I really dislike this solution as it requires you to go mucking about in the Registry, so if anyone else has something a bit more elegant, please let me know in the comments.

Also I believe that Excel of all things, being a Microsoft product, should have one of the smoothest SQL importing/exporting experiences. But meh. I guess it is what it is.

References:

• https://docs.microsoft.com/en-us/sql/integration-services/load-data-to-from-excel-with-ssis?view=sql-server-2017#truncated-text
• https://www.concentra.co.uk/blog/why-ssis-always-gets-excel-data-types-wrong-and-how-to-fix-it

DISCLAIMER: As stated, I’m not an expert so please, PLEASE feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)