3

I have an SQL server 2019 with databases and tables all set with the collation Latin1_General_100_CI_AS_SC_UTF8.

The relevant table has code and desc columns which are both of data type varchar.

In SSIS project I have a single Data Flow component:

I have a UTF-8 CSV file read with flat file connection, text column code to match is DT_STR, 65001

I have a Lookup that is set to "Full Cache" and loads the Latin1_General_100_CI_AS_SC_UTF8 table, but SSIS thinks the varchar columns are DT_STR, 1252

Finally the code in both CSV and lookup are matched and desc is sent to destination table which is on the same Latin1_General_100_CI_AS_SC_UTF8 collation. The destination component is set to AlwaysUseDefaultCodePage True and DefaultCodePage 65001.

I now get an error saying the column has more than one code page and cannot run the package.

If not for the mislabeled 1252, this package should run. I believe its something to do with ExternalMetadataXml, which is read-only and says all my lookup varchar columns are CodePage="1252".

If I manually edit the package .dtsx with npp and replace all instances of 1252 with 65001, the package can run and seems to do what I expected, as long as I never touch the lookup component again.. That seems a bit messed up of a solution tho, I am hoping there's someone else who has a cleaner way to fix this. Thanks.

edit 2021/03/10: I thought I should clarify, the lookup is using OLE to read a table from the SQL server, and is trying to match code column onto the code column read from the flat file. The problem is not the flat file, which is already recognised as DT_STR, 65001. The problem is the Lookup widget reading from a table that should be 65001, somehow ends up with 1252. If I didn't use OLE, and instead read from a UTF8 flat file to create the Lookup widget, there is no error.

2
  • Hi there. 1) Which column, exactly, is getting the "column has more than one code page" error? 2) have you tried setting AlwaysUseDefaultCodePage to False? 3) In the "Flat File Connection Manager Editor (General Page)", is the file set to code page 65001 or Unicode (should be 65001)? 4) are both columns in the Lookup Component configured as DT_STR, 65001? And, 5) is your CSV file using a BOM (i.e. does Notepad++ show in the bottom, right "UTF-8", "ANSI", or "UTF-8-BOM")? If not "UTF-8-BOM", trying converting file to "UTF-8-BOM" (might influence external metadata). – Solomon Rutzky Mar 6 at 6:46
  • Hello again. Thanks for adding clarification to the question. More questions: 1) where exactly does 1252 show up in the .dtsx file? Is it in the ConnectionManager used by the Lookup Component? 2) You say that "The destination component is set to AlwaysUseDefaultCodePage True and DefaultCodePage 65001" but what about the source properties? 3) For either source or destination connection properties, do either have "Unicode" set to 1 / checked? 4) You mention that the lookup is using "Full" cache. Have you tried using "No Cache"? – Solomon Rutzky Mar 11 at 17:15
2

I found these tidbits of information while reading through the collation documentation:

For example, for the OS locale "English (United States)" (code page 1252), the default collation during setup is SQL_Latin1_General_CP1_CI_AS, and it can be changed to its closest Windows collation counterpart, Latin1_General_100_CI_AS_SC.

Reference: Collation and Unicode support (Microsoft | SQL Docs)

...So the collation Latin1_General_100_CI_AS_SC does relate to the Windows code page 1252...

SQL Server 2012 (11.x) introduced a new family of supplementary character (_SC) collations that can be used with the nchar, nvarchar, and sql_variant data types to represent the full Unicode character range (000000–10FFFF). For example: Latin1_General_100_CI_AS_SC or, if you're using a Japanese collation, Japanese_Bushu_Kakusu_100_CI_AS_SC.

Reference: Collation and Unicode support - Supplementary characters (Microsoft | SQL Docs)

... now I know where the _SC comes from and reading on ...

SQL Server 2019 (15.x) introduces full support for the widely used UTF-8 character encoding as an import or export encoding, and as database-level or column-level collation for string data. UTF-8 is allowed in the char and varchar data types, and it's enabled when you create or change an object's collation to a collation that has a UTF8 suffix. One example is changing LATIN1_GENERAL_100_CI_AS_SC to LATIN1_GENERAL_100_CI_AS_SC_UTF8.

Reference: Collation and Unicode support - UTF-8 support (Microsoft | SQL Docs)

... and now we have a matching collation: LATIN1_GENERAL_100_CI_AS_SC_UTF8.

Along the line it all sums up to the Windows code page being 1252 and the SQL Server collation being UTF-8.

Your Fix

Hacking the SSIS package seems to fix the wrong interpretation of the flat file, but only until the package is edited/modified again.

Could it be that the TXT file isn't being interpreted as 100% UTF-8?

TXT files and UTF-8

I saved the first couple of lines of this answer into a .txt file and had a look at the contents in a Hex-Editor:

Hex of Notepad UTF-8

00000000h: 49 20 66 6F 75 6E 64 20 74 68 65 73 65 20 74 69 ; I found these ti
00000010h: 64 62 69 74 73 20 6F 66 20 69 6E 66 6F 72 6D 61 ; dbits of informa
00000020h: 74 69 6F 6E 20 77 68 69 6C 65 20 72 65 61 64 69 ; tion while readi

There doesn't seem to be a UTF-8 BOM header there.

Hex of Notepad ANSI

00000000h: 49 20 66 6F 75 6E 64 20 74 68 65 73 65 20 74 69 ; I found these ti
00000010h: 64 62 69 74 73 20 6F 66 20 69 6E 66 6F 72 6D 61 ; dbits of informa
00000020h: 74 69 6F 6E 20 77 68 69 6C 65 20 72 65 61 64 69 ; tion while readi

So both ANSI and UTF-8 are stored the same way when using Windows NOTEPAD.EXE.

I do know that a UTF-8 file sometimes comes with a BOM (Byte Order Mark), that specifically denotes the file as UTF-8.

This looks like this:

00000000h: EF BB BF 49 20 66 6F 75 6E 64 20 74 68 65 73 65 ; I found these
00000010h: 20 74 69 64 62 69 74 73 20 6F 66 20 69 6E 66 6F ;  tidbits of info
00000020h: 72 6D 61 74 69 6F 6E 20 77 68 69 6C 65 20 72 65 ; rmation while re
00000030h: 61 64 69                                        ; adi

This basically shouts: In case you don't know, I am a UTF-8 encoded file...

But ...

The UTF-8 BOM is a sequence of bytes at the start of a text stream (0xEF, 0xBB, 0xBF) that allows the reader to more reliably guess a file as being encoded in UTF-8. [...] Normally, the BOM is used to signal the endianness of an encoding, but since endianness is irrelevant to UTF-8, the BOM is unnecessary.

Reference: Byte order mark (Wikipedia)

Possible Conclusion

Going back on the quoted information in the introduction...

...SQL Server 2019 (15.x) introduces full support for the widely used UTF-8 character encoding as an import or export encoding, and as database-level or column-level collation for string data. UTF-8 is allowed ....

What is the definition of full support for the widely used UTF-8 character encoding? With or without BOM? We don't know so let's try another approach.

Possible Solution

I suggest you grab a tool that supports saving TXT files as UTF-8 and which will insert the BOM.

This way you ensure that the SSIS package will interpret the TXT file correctly as UTF-8 and will not convert the package to interpret Code-page 1252. This way you shouldn't encounter any issues with your SSIS package.

8
  • hi, thanks for your response, the flat file is read as UTF8, with the correct 65001 code page. issue is with the OLE lookup, SSIS thinks the server is providing 1252 when the column on server is VARCHAR UTF8 collation. Inspecting the column code page on SQL yields 65001 – softwarenewbie7332 Mar 10 at 7:44
  • Could you provide details in your question on how you verified that the file is being read as UTF-8? Again if the server is located in the United States then the locale is determined to be codepage 1252. If the file does not contain the BOM then it will be "tried" to be read as UTF-8. It can however still be deemed to be codepage 1252. Did you try saving your file as UTF-8 with BOM? Did you open the file and look at the first couple of bytes? SSIS and UTF-8 are pretty new paired with the new _SC_UTF8 collations. You might be hitting a built-in limitation/bug. – John K. N. Mar 10 at 8:52
  • SSIS lets you specify UTF8 65001 as the code page, see the first image of billinkc's answer – softwarenewbie7332 Mar 10 at 8:59
  • Yes, I am aware of that fact. – John K. N. Mar 10 at 9:15
  • Hello @softwarenewbie7332 and John: Just FYI, the statement, "Along the line it all sums up to the Windows code page being 1252 and the SQL Server collation being UTF-8.", is incorrect. This is a Windows collation, not a SQL Server collation (as that would have a name starting with SQL_). Also, it's impossible to be both code page 1252 and UTF-8 at the same time. Both are 8-bit encodings, and UTF-8 is code page 65001, and you can only have 1 code page as that is the encoding for that column or expression. The collation specified by the O.P. is correct and doesn't imply code page 1252. – Solomon Rutzky Mar 11 at 6:15
2

With the disclaimer that I'm a "dumb American" who doesn't deal with non-English data but did work with a friend recently on using bulk import with UTF-8 data, here's what I see.

I have a pipe separated value file that looks like this

level|name
7|"Ovasino Poste de Santé"

Notepad++ indicates I have saved it as UTF-8.

I created two flat file connection managers in SSIS: Codepage65001STR and Codepage65001WSTR. They both use a Code Page of 65001 (UTF-8)

enter image description here

In the advanced tab for the STR variant, I left the data type as DT_STR enter image description here

In the advanced tab for WSTR variant, I changed the data type to DT_WSTR

enter image description here

I also created a table and loaded it with the same data

DROP TABLE IF EXISTS dbo.dba_286478;
CREATE TABLE dbo.dba_286478
(
level int NOT NULL
,   name varchar(75) COLLATE Latin1_General_100_CI_AS_SC_UTF8
)
INSERT INTO dbo.dba_286478
(
    level
,   name
)
VALUES
(
    7 -- level - int
,   'Ovasino Poste de Santé' -- name - varchar(75)
);

DROP TABLE IF EXISTS dbo.dba_286478;
CREATE TABLE dbo.dba_286478
(
    level int NOT NULL
,   name varchar(75) COLLATE Latin1_General_100_CI_AS_SC_UTF8
);

I then created a data flow task with a Flat File Source using the different Flat File Connection Managers and added data viewers between them and an empty derived column (so I had an anchor point for the data viewer).

I did the same thing with an OLE DB Source pointing at my table as well as a custom query of

SELECT
    T.level
,   CAST(T.name AS varchar(75)) AS name
FROM 
    dbo.dba_286478 AS T;

as well as explicitly defining the collation as it makes no different in SSIS

,   CAST(T.name COLLATE Latin1_General_100_CI_AS_SC_UTF8 AS varchar(75)) AS name

The results all show the same, the final word is an accented Sante. If the UTF-8 hadn't happened, it'd show as Santé

enter image description here

At this point, it doesn't matter whether we DT_STR or DT_WSTR in our flat file source column definition, the component understands UTF-8 and UTF-16.

Properties, metadata of each. The Codepage 65001 STR looks as expected. code page of 65001 and data type DT_STR

enter image description here

Unicode, DT_WSTR looks good

enter image description here

The OLE components however, they're a different animal. The component is returning a metadata of DT_WSTR (full Unicode/UTF-16) regardless of whether we do an explicit cast to DT_STR, optionally specifying collation, or let the natural metadata flow through.

Either way, it doesn't detect the code page/collation stuff and just says Nope, you're Unicode

enter image description here

So, when we get to trying to use a Lookup task with an OLE DB connection manager, we can expect and receive the same inability to delineate between UTF-8 string/varchar and UTF-16/nvarchar

enter image description here

The error would indicate and that's true, DT_STR can't match DT_WSTR

Cannot map the input column, 'name', to the lookup column, 'name', because the data types do not match.

So what do I do?

You must have type alignment to make the lookup component work which means the source data needs to be of type DT_WSTR. You can either bring the data in from the Flat File as Unicode or leave it as string with code page 65001. If you go the latter route, then you need to make a copy, Derived Column or Data Conversion work, of that column and use it in the Lookup component.

If you're pulling text out of the lookup component, that's now in your pipeline as Unicode so you probably want to then convert that to a string type with code page. Again, Derived Column or Data Conversion will be used.

enter image description here

SSIS OLE components don't understand UTF8

We saw with the source and lookup component that SSIS is going to treat the UTF-8 strings as UTF-16 but I assumed it would handle storing to the table just fine. Not so much.

My server collation is Latin1_General_100_CI_AI_SC_UTF8 and while I switched accent sensitivities between server and table definition of dbo.dba_286478, it doesn't matter in this case as it's UTF-8 all the way down.

For my Flat File Source, I use the STR based file which has the metadata shown above with the yellow highlighting. The Codepage 65001 for data type DT_STR is what we want.

I added an OLE DB Destination and pointed it at my table which again has the "name" column defined as UTF-8

name varchar(75) COLLATE Latin1_General_100_CI_AS_SC_UTF8

Check this error!

enter image description here

Validation error. Data Flow Task OLE DB Destination [138]: The column "name" cannot be processed because more than one code page (65001 and 1252) are specified for it.

We only have code page 65001 at play in this data flow and yet, something in SSIS space is inferring/defaulting to a 1252 code page during validation.

Making it work

The componentry in a data flow task was built with OLE DB connections in mind. That's why the Lookup task supported OLE DB Connections for 2005, 2008 and maybe 2008R2? Long time ago now, I know but the Cache Connection Manager (aka anything else) option was added in later iterations because of the need to use something besides OLE connection managers especially given the push then was to deprecate the OLE driver.

An ADO.NET Connection Manager does slightly better than OLE in this case and that's likely what you're going to have to use to work with UTF8 data in an SSIS package. It will be implicitly converting to UTF-16 when it presents to the table and then SQL Server is going to snap it back into UTF-8 space (best I can tell).

For reference, bringing UTF-8 data into the pipeline with ADO Source will still be flagged as DT_WSTR/UTF-16/unicode.

enter image description here

But you can land DT_STR code page 65001 to an ADO.NET Destination without a code page mismatch error like I'm seeing for OLE DB Destination.

enter image description here

The data from the database is going to appear as DT_WSTR regardless of how you bring it into the pipeline. That means you can define and OLE and an ADO connection manager to use the Lookup component as is.

Or you can add a precursor data flow step to populate the Cache Connection Manager and only have an ADO.NET connection manager. Were you to go that route, convert the DT_WSTR data to DT_STR with codepage 65001 and store that data into the cache.

DFT - Populate Cache -> DFT - Load data

DFT - Populate Cache

ADO.NET Source -> Data Conversion -> Cache Connection Manager

DFT - Load Data

Flat File Source -> Lookup Component -> ADO.NET Destination
    

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.