cannot transfer unicode to non-unicode string ssis

Today I face the following Error, when I transfer data from excel file to sql server server through SSIS package.

Problem is that in excel data is stored in Unicode. And  transfer data to field of sql server which is define as varchar.

 

Solution 1. Change the sql data type   to nvarchar instead varchar. Unicode sql table will be define as

 

CREATE TABLE [dbo].[Product](

[ProductName] [nvarchar](50) NULL,

[ProductDetail] [nvarchar](50) NULL

) ON [PRIMARY]

Now try again. Data will successfully migrate. But this not good solution.

 

Solution 2

Work around to this problem is use data Conversion task between the excel source and destination sql server  As

 

So to get around this problem we have to also use a Data Conversion task.

 

alirazazaidi

Ali Raza Zaidi, Microsoft ERP Consultant, Currently working for Business Experts Gulf in United Arab Emirates. Specialized in HR & Payroll, Supply Chain and Financials modules.