Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSIS 2005 for French accents Expand / Collapse
Author
Message
Posted Thursday, May 2, 2013 9:28 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 3, 2013 3:44 PM
Points: 2, Visits: 18
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.
Post #1449035
Posted Friday, May 3, 2013 1:04 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 1:21 PM
Points: 809, Visits: 1,160
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.
Post #1449332
Posted Friday, May 3, 2013 1:16 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:57 AM
Points: 7,084, Visits: 12,577
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
Post #1449339
Posted Friday, May 3, 2013 3:52 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 3, 2013 3:44 PM
Points: 2, Visits: 18
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.


  Post Attachments 
test_french.txt (3 views, 492 bytes)
layout.xlsx (1 view, 10.47 KB)
Post #1449365
Posted Monday, May 20, 2013 9:20 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:57 AM
Points: 7,084, Visits: 12,577
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


  Post Attachments 
test_french.jpg (54 views, 7.63 KB)
test_french2.jpg (53 views, 19.19 KB)
Post #1454584
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse