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.