Hi, I have a file encoded in UTF8 format which i have loaded into a SQL server table. One of the columns has Country Region names which I have stored in a column of data type NVARCHAR(200). Now some of the names being displayed in the column are have special characters. Eg - "São Paulo" I know this is "Sao Paulo" in Brazil. I also understand since this is encoded in UTF8 it is being displayed in this format, and there is a way to display it correctly in ASCII format. Can someone guide me as to how we can convert UTF8 into ASCII in SQL server/SSIS?? many Thanks P

Add a data conversion task between the Flat File source and the SQL Server target in your DFT. Inside the Data Conversion task this column needs to be set to convert to Unicode String[ DT_WSTR ]Arthur My Blog

This column in the source file (while reading from the file) is already defined as unicode string. converting it to Unicode String (DT_WSTR) still retains its data type as unicode. My end goal is to store and display "São Paulo" as "Sao Paulo" in the SQL database. (the column in SQL database would be VARCHAR) Please correct me if my understanding is wrong.

Thanks for the answers. What I have discovered is when reading the file using a flat file connection manager, the encoding (code page) needs to be set to 65001 (UTF-8). Once this is done, we are making sure that the characters are read in the correct format, and then once we load the data into a sql table (into NVARCHAR field), the characters are displayed correctly. So all that one needs to do in this scenario is set the correct code page while reading the source file.