This CTE solved the problem:

CREATE TABLE TestPivot (ID int, Name nvarchar(50))

INSERT INTO TestPivot VALUES (1,'aaa')

...

Thank you all for replying.

Making the following changes solved the issue:

1. Setting Text Qualifier of Flat File Connection Manager to ".

2. Using REPLACE(REPLACE(Column,",",""),"\"","") in Derived Column Transformation.

Thank you all for your reply!

I modified the expression to fetch date directly from the XML file, rather than swapping mm and dd in SQL server table itself. This did...

Very helpful article! Simple and well-explained. Thanks for posting it!!!

I solved this issue by converting the encoding in my C# page as follows:

//Define Windows 1252, Big5 and Unicode encodings

...

No problem Sean!

Thank you for taking some time out for this!

Hi Sean,

Which version of Access are you using?

I have to transfer data from Access 97, where Memo field uses Double-byte encoding. If you are using recent versions of Access,...

Hi James,

Thanks for your reply. But, I agree with Sean. Nevertheless, I implemented your suggestion. Inserting non-English characters from Memo field to nText field still inserts ??? in SQL Server...