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

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

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 from the 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

Leave a Comment
  • Please add 1 and 3 and type the answer here:
  • Post
Wiki - Revision Comment List(Revision Comment)
Sort by: Published Date | Most Recent | Most Useful
Comments
  • SathyanarrayananS edited Revision 2. Comment: added code block

  • Maheshkumar S Tiwari edited Revision 1. Comment: Added horizontal row above See also and minor edit

  • Maheshkumar S Tiwari edited Original. Comment: Added tags

Page 1 of 1 (3 items)
Wikis - Comment List
Sort by: Published Date | Most Recent | Most Useful
Posting comments is temporarily disabled until 10:00am PST on Saturday, December 14th. Thank you for your patience.
Comments
  • Maheshkumar S Tiwari edited Original. Comment: Added tags

  • Maheshkumar S Tiwari edited Revision 1. Comment: Added horizontal row above See also and minor edit

  • SathyanarrayananS edited Revision 2. Comment: added code block

Page 1 of 1 (3 items)