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