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

Mini Post: Error 0xc02020a1: Data conversion failed. The data conversion for column returned status value 4

Helloooo! And welcome (or welcome back) to SQL Something!

Today's mini-post is about an error you can run into when attempting to use the Import/Export Wizard to import some data into a DB of your choice:

Error 0xc02020a1: Data Flow Task 1: Data conversion failed. The data conversion for column "COLUMN NAME" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".

Fig. 1: Annoying Error.

Recreate a 2008 DB on a 2005 Instance

Hey everyone, just a quick note on one way to get data from a 2008 DB to a 2005 DB. (EDIT: This initial "Mini Post" turn out a bit longer than I expected...)

As most of you would know you cannot restore a backup from a newer version of SQL Server on an older instance, so this limits the options you have if you do end up having to this for whatever reason.

Your best option is to generate a script of the DB on the newer instance and execute the script on the older instance.

You can choose to either script the data as well, or you can export the data to either a text file or Excel document and import the data into the 2005 DB afterwards.