SSIS: Cannot convert between unicode and non-unicode string data types during import

Here is an issue you may receive if you try to import data from an Excel spreadsheet into a Sql Server database table:

 

Error at Data Flow Task [OLE DB Destination [23]]: Columns “firstname” and “FirstName” cannot convert between unicode and non-unicode string data types.

What is this error?  Excel data is usually stored in a Unicode format, but a database field can setup either be Unicode or Non-Unicode.  To read some of the differences for databases, please follow this link:

https://technet.microsoft.com/en-us/library/ms189617(v=sql.105).aspx

 

Below is a picture of our Excel file data, which is automatically unicode:

 

Below is how our database table that we want to import this data into is setup.  You can see that the Firstname column was setup as Varchar instead of Nvarchar.  You can rebuild the table to make the FirstName column Nvarchar, but you do not have time right now to go through quality assurance to make sure the programs accessing this table are not affected.  Just do not make any structural changes to your tables without doing full testing before going into production.

 

When we look at our SSIS package that was created to import the spreadsheet, we can see we have a warning saying the Firstname column cannot be converted to Unicode.

 

If you ignore this error and try and run the package anyway, you receive the following error:

TITLE: Package Validation Error
------------------------------

Package Validation Error

------------------------------
ADDITIONAL INFORMATION:

Error at Data Flow Task [OLE DB Destination [23]]: Columns "firstname" and "FirstName" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task [SSIS.Pipeline]: "OLE DB Destination" failed validation and returned validation status "VS_ISBROKEN".

Error at Data Flow Task [SSIS.Pipeline]: One or more component failed validation.

Error at Data Flow Task: There were errors during task validation.

 (Microsoft.DataTransformationServices.VsIntegration)

------------------------------

 

Fixing

Here is how to fix this so you can get the data imported into your database.  We need to add a Data Conversion task from our SSIS Toolbox between the Excel Source and the OLE DB Destination.  This will allow us to convert the data from Unicode to Non-Unicode.

 

If you double click the Data Conversion item, you can now tell it what you want to convert.  In our case, SSIS could not convert the Firstname to import into the database, so the Input Column will be firstname.  For Output Alias, you can use the default name, but I changed ours to Converted_FirstName so I can find it easier.  We need to change the Data Type to [DT_STR] because we want to convert the spreadsheet data into a Varchar format.  We left length at 255 since that is what our database table is currently set at.  Press OK when you are done.

 

Now double click the OLE DB Destination on the Data Flow screen so we can modify the mappings.  Below is how it is currently setup.

 

We need to modify the input column from firstname to the new data conversion field called Converted_Firstname.  When done, press OK.

 

We see that the OLE DB Destination no longer has a warning, so we run it below and we see that all of the data got imported into our table!

Some of the messages/errors you receive using SSIS can be very confusing, but this one is relatively straight forward, and now you know how to fix it.