September 24, 2014 at 10:51 am
My advice:
1) on destination table never use char or varchar. Instead use Unicode nvarchar or nchar
2) do not copy data directly from flat files source or excel file source to OLE DB destination and instead use 'Data Conversion' object in 'Data Flow' task of SSIS
November 18, 2014 at 2:59 am
Used Wizard to import 1 of 3 FLAT FILE CSV files; created destination table.
Used Wizard to import 2 of 3 FLAT FILE CSV files; specified destination created in first run.
SAVED package on File System to use for 3 of 3 FLAT FILE CSV files; ran it.
This CODEPAGE mismatch occurred.
How do you reconcile this?
What must I do know, but spend non-productive time resolving MS problems with schizophrenic Wizards.
Well, this at least details how to replicate the MS error... so don't do it. 🙂
And this is 2014, years after the Connect bug was raised... so it must be by design, right? 😀
November 18, 2014 at 3:06 am
Got it. It relates to Locale.
UTF-8 has code page 65001
ANSI Latin-1 has code page 1252
Not sure why this has to be checked, but
ensure the same locale is used.
😀
I defeated MS muddle today
November 18, 2014 at 3:50 pm
Technically this isn't a bug per se. It primarily arises because of the various encodings of text files, this is not something that will ever go away and is well beyond Microsofts ability to resolve. There are several main standards used for encoding in let's call it 'western' countries, though this is an oversimplification and it has more to do with the languages/characters that are being encoded.
ANSI/ASCII are historic character sets for primarily English text (there are many other 6,7 & 8 bit variants), UTF-8 and UTF-16 are Unicode extensions upon this simple set to extend to most known written characters across languages/cultures. However, before the emergence of such Unicode standards each language tended to get it's own code-page/encoding.
All that being said, UTF-8 (without BOM) and ANSI appear exactly the same to a machine if there is no characters used beyond the first 128. So Visual Studio has to make a guess unless you tell it what to expect or systems start adding the BOM to UTF-8 files when they are created. In your case the problem has been created by whatever system created the text file not adding 2bytes at the start of the file (the BOM) saying this is a UTF-8 file.
March 18, 2016 at 1:17 pm
I AM SO EXCITED TO SHARE WITH YOU GUYS THE FIX!
I was reading through all of the posts, and attempted to delete and recreate both my flat file source connections and OLE DB destinations. This didn't work. I changed the default code page number in the OLE DB destination properties pane and that still didn't help.
THE FIX: When you edit the flat file source connection, below the space that you brows to your file there is a code page drop down. It was set to 65001 (UTF -8) and all I had to do was select the 1252 (ANSI- LATIN I) option and save. then I pressed the build button on to refresh the meta data and it worked!
May 17, 2018 at 7:33 am
This one, while nearly ancient history now, keeps hangin' around like a dog you made the mistake of feeding. A warning to those who "fixed" the problem by simply changing the source file code page from 65001 to 1252 and magically it worked again. Yes, your data loaded, but likely incorrectly. If your data has anything outside of the letters a thru z and digits 0 thru 9, you are going to have some funky looking characters when your dashes, apostrophes, commas and such get mapped to strange Arabic, Asian or unknown characters.
bochambers has posted the only complete and CORRECT explanation and solution to the issue.
July 2, 2018 at 11:06 am
This was removed by the editor as SPAM
December 28, 2018 at 3:19 pm
shouold i try to re-create the CSV file or text file make it text file and copy and paste or recreate the data in new csv file and try. sometimes if file or data is in different format or some thing it errors just my guess. should i try that too ? or it is a waste of time?
Likes to play Chess
December 28, 2018 at 6:27 pm
Just change the code in the properties.....
Go to the day flow task.... and right click on properties.... set the codes here.. as the warning says.
Then build... it will now always work.
Just an ssis setting.... I'm not at my pc or I'll show you.
September 5, 2019 at 6:43 pm
Observation: The version of VS I'm working w/ automatically defaults to 65001 when I bring up the connection manager editor. If you change to 1252 in the properties and then bring up the editor, it will go back to 65001 unless you cancel out of the editor or change it back there.
Scottczech is not exactly correct. Anything you see on a standard English keyboard is included in the standard 128 ascii character set. If you are dealing w/ languages outside of English, or your data has characters not represented on a standard English language keyboard.
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply