Share via

SSIS: Import Excel to table - Cannot convert between unicode and non-unicode string data types

  • Article

When data is imported from Excel to SQL Server table using SSIS and if the destination table has column of data type VARCHAR, we will end up with error

"Cannot convert between unicode and non-unicode string  data types".

 As far as my knowledge, I know there are two solutions:

i) By adding transformations Data Conversion & Derived Column between source & destination and getting the converted data type from source to match with destination.

ii) By altering the VARCHAR type in a column of destination table to NVARCHAR type.

 
Consider if we are importing more than 50 to say maximum number of columns from Excel, we have to manually do it for each columns in Data Conversion task.

We can also edit the package dtsx XML through Xquery & XML DML and do some manipulation but it would be a complex process again!!

For example: (Consider below scenario )

Input Excel :

ID   DOJ   Name
1   41275  Sathya
2   41276  Deepak
3   41277  Lakx

--import to table1 will fail (Cannot convert between unicode and non-unicode string  data types)

CREATE TABLE  Test1(Id INT,DOJ DATE,Name VARCHAR(100))

--import to table2 will succeed (character column of NVARCHAR type)

CREATE TABLE  Test2(Id INT,DOJ DATE,Name NVARCHAR(100))

 This is the most FAQ in MSDN SSIS forum.

So I would suggest the approach of setting column of type NVARCHAR in the destination table as correct,
easier and better solution, after discussing here .

We can easily generate the script  to alter columns of type VARCHAR to NVARCHAR of destination table as shown below :

SELECT 'ALTER TABLE '+OBJECT_NAME(C.OBJECT_ID) +' ALTER COLUMN '+ C.name +' NVARCHAR(100)'
FROM sys.columns AS C 
JOIN sys.types AS T ON C.user_type_id=T.user_type_id 
WHERE T.name = 'VARCHAR'
AND OBJECT_NAME(C.OBJECT_ID) = 'Test1'
GO

 

See Also