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)
Test2(Id
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
T
ON
C.user_type_id=T.user_type_id
WHERE
T.
=
'VARCHAR'
AND
OBJECT_NAME(C.OBJECT_ID) =
'Test1'
GO
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