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 ««12

The column "Column 2" cannot be processed because more than one code page (65001 and 1252) are specified for it. Expand / Collapse
Author
Message
Posted Tuesday, April 2, 2013 1:01 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 11:56 AM
Points: 10, Visits: 106
In your dataflow right click your OLE DB Destination and click properties.
In your Properties pane which might be on the right side.
Under Custom Properties there is an option called AlwaysUseDefaultCode to True.
Manually change your DefaultCodePage to the code that it says it can't convert. So in my scenario it said it couldn't do pages 65001 to 1295. I changed the DefaultCodePage 65001. From there your Destination will have a "Warning" on it but you should be able to execute the package now.
Post #1438064
Posted Wednesday, May 15, 2013 3:36 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 3:33 PM
Points: 102, Visits: 80
This is a really old post, but is still the highest hit when it comes to searching google for this issue so I'll try to explain.
A code page in SSIS corresponds to the specific character encoding used e.g. ASCII is code page 1252 and UTF-8 is code page 65001. SQL Server stores character data using "Collations" corresponding to different code pages. A character encoding is the method in which characters are stored as binary data.
If you are using an OLE DB Destination where the actual destination is a SQL Server instance then you may encounter this error for several reasons.
1. Your "source" text file is UTF-8 and your destination columns are VARCHAR/CHAR datatypes. Note that most UTF-8 (99.9%) files contain unicode data so you should use NVARCHAR/NCHAR datatypes for the destination columns.
2. Your "source" text file is ASCII (code page 1252) and your destination columns are VARCHAR/CHAR datatypes but have a collation that supports double byte characters e.g. Korean_Wansung_CI_AS (code page 949), (I'm not sure if any of the SQL Server VARCHAR collations actually use code page 65001 though). If you never specified the collation when creating the table then you should check to see what the databases default collation is. You can fix the issue by altering the collation of the columns in the destination table to something such as Latin1_General_CI_AS, but I would avoid this if you can't say for certain that there isn't and won't be any non-ASCII characters stored in the data.

Be careful changing code pages if you don't know what they stand for. If SSIS automatically uses a code page of 65001 then you should not change the code page to 1252 as this will cause a corruption of all unicode data in the file, you will know this has happened if you end up with text that looks a bit like this "ㄴㅇ리ㅏ호댜ã…".

I've experienced many frustrations with similar issues to this, so can only hope that this post might save someone else from a little bit of pain.
Post #1452995
Posted Thursday, June 20, 2013 7:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 7, 2014 3:50 AM
Points: 2, Visits: 37
In the flat file connnection manager editor, set code page property to 1252(ANSI-LATIN 1).
As it changes some time to other setting and gives this error.
Post #1465701
Posted Tuesday, July 2, 2013 3:49 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 10:52 AM
Points: 151, Visits: 452
Thanks much! this worked like a charm to me.....
Post #1469782
Posted Wednesday, November 20, 2013 11:26 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 27, 2014 6:02 PM
Points: 1, Visits: 3
The likely issue is that you have a source and target that are two different data types (ANSI and UTF8).

Perhaps you ran an SQL statement to dump data from a table directly to disk and it defaulted to the UTF8 format, whereas your SSIS job is trying to load it to ANSI.

Typically, I have to convert whatever flat file I'm using to ANSI (if small, open it in notepad and save it as ANSI) in order to get rid of this issue, but you may be able to get around it in SSIS.

Darn, should have read through to page 2 before posting.
Post #1516156
Posted Wednesday, September 24, 2014 10:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 26, 2014 8:25 AM
Points: 3, Visits: 4
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
Post #1619346
Posted Tuesday, November 18, 2014 2:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 5:45 AM
Points: 9, Visits: 19
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?
Post #1635883
Posted Tuesday, November 18, 2014 3:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 5:45 AM
Points: 9, Visits: 19
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.

:D

I defeated MS muddle today
Post #1635887
Posted Tuesday, November 18, 2014 3:50 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 3:33 PM
Points: 102, Visits: 80
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.
Post #1636229
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse