SSIS 2005 for French accents
-
I am running into issues with loading a UTF-8 flat file containing French characters using SSIS 2205 to a SQL 2005 database table. The french accents are lost after loading the file to SQL table.
Can someone please help with the steps to follow ? Please see below steps followed
1. Flat file is in UTF-8 format.
2. SQL table (destination) data type is Nvarchar
3.Flat file connection is set to read file in 65001(UTF-8) and column is defined as Unicode string [DT_WSTR]
4. collation is Latin1_General_CI_AS
The package runs OK , but the final results are missing the French characters.
-
You have to specify N before literals to say that to SQL that this is unicode.
Like in the below example :-
insert into Test(str)values(N'gîte')-- this is word for house in french.
-
Are you issuing a series of INSERT statements or are you using something like an OLE DB Destination from SSIS? If the former, then check the N prefix mentioned earlier. If you are using the latter, could you please post a sample file (you can attach it as a text file to this thread) and confirm the version of SSIS you are using? I would be curious to try and recreate the issue on this side.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
I am using Flat file connection in SSIS 2005 . The input file has defined fixed width for each column , so when i try to load the UTF-8 file with flat file connection encoding as "UTF-8 65001" the column gets shifted by 1 position when a special charcter like "Accent" is encountered.
As a workaround , now im saving the flat file as "Unicode" and then change the SSIS flat file connection manager to "Unicode" by selecting the check box. It works OK, but im trying to find out if there is any way i can consume the UTF-8 flat file directly without having to convert to Unicode.
The input file is generted in Unix platform , and the source team can provide me either UTF-8 or UTF-16 to support bilingual charcters. Not sure , what "Unicode" type in SSIS translates to in Unix and if it can be generted in the format that i am converting the file to.
Attaching a sample input file and layout.
-
Sorry for the delay. The email notification that you posted more info slipped by me and got buried in my inbox.
I opened the .txt file you attached but it does not include any characters with French accents.
The input file is generted in Unix platform , and the source team can provide me either UTF-8 or UTF-16 to support bilingual charcters. Not sure , what "Unicode" type in SSIS translates to in Unix and if it can be generted in the format that i am converting the file to.
In Windows/SSIS lingo "Unicode" refers to the UCS-2 encoding which is a subset of UTF-16LE. If your Unix data provider is willing to send you the file in UTF-16 format I would take them up on that as it will likely keep you from having to convert the file manually before importing. To import a file in UTF-16 format you would set your Flat File Connection in your SSIS Package to import it as "Unicode", i.e. the code page becomes irrelevant and you would check the "Unicode" checkbox.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply