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...

SSIS Error: Cannot Convert Between Unicode and Non-Unicode Data Types

Hellooo out there! Welcome back to (the not quite monthly) SQL Something!

This month we are looking at a conversion error I ran into when trying to alter an old SSIS package:

Fig. 1: Error!

Fig. 2: Change isn't easy...

Finally an error that's pretty self explanatory! The error says it can't convert from non-Unicode to Unicode, so obviously we need to find some way to make the two the same type. :-)

Mini Post: SSIS Excel Destination Failed Validation And Returned Error Code 0xC020801C

Hey everyone! Welcome back to SQL Something!

Before the month is up (and I apologize for missing last month) let's get in a quick post. This time we look at a simple SSIS validation error: "SSIS Excel Destination Failed Validation And Returned Error Code 0xC020801C".

This little guy popped when I attempted to export data to an Excel Destination in SSIS:

Fig. 1: Final Destination

Looking up the error code can yield the following: "SSIS Error Code DTS_E_OLEDB_EXCEL_NOT_SUPPORTED: The Excel Connection Manager is not supported in the 64-bit version of SSIS, as no OLE DB provider is available.".

The error was produced because excel does not have a 64 bit driver. If your OS is 64 bit, SSIS will, by default, attempt to run all drivers as 64 bit. It is simply a matter of going to the project properties and changing the Run64BitRuntime property to FALSE:

Fig. 2: Finding Properties

Fig. 3: Make the change to FALSE

And that's all there is to 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. :-)