Sql-Server

如何將 SSIS 查找轉換中的程式碼頁修復為 65001？

  • March 10, 2021

我有一個 SQL Server 2019，其中的數據庫和表都設置了排序規則 Latin1_General_100_CI_AS_SC_ UTF8。

相關表的code和desc列都是數據類型varchar。

在 SSIS 項目中，我有一個數據流組件：

我有一個使用平面文件連接讀取的 UTF-8 CSV 文件，code要匹配的文本列是DT_STR, 65001

我有一個設置為“完整記憶體”的查找並載入 Latin1_General_100_CI_AS_SC_UTF8表，但 SSIS 認為varchar列是DT_STR, 1252

最後，code在 CSV 和查找中匹配desc並發送到相同 Latin1_General_100_CI_AS_SC_UTF8 排序規則的目標表。目標組件設置為AlwaysUseDefaultCodePage True和DefaultCodePage 65001。

我現在收到一條錯誤消息，指出該列有多個程式碼頁並且無法執行該包。

如果不是因為貼錯標籤的 1252，這個包應該執行。我相信它與ExternalMetadataXml，它是只讀的，並且說我所有的查找 varchar 列都是CodePage="1252".

如果我用 npp 手動編輯包 .dtsx 並用 65001 替換 1252 的所有實例，只要我不再觸摸查找組件，包就可以執行並且似乎符合我的預期。這似乎有點搞砸了一個解決方案，我希望還有其他人有更清潔的方法來解決這個問題。謝謝。

編輯 2021/03/10：我想我應該澄清一下，查找是使用 OLE 從 SQL 伺服器讀取表，並試圖將code列匹配到code從平面文件讀取的列。問題不在於平面文件，它已被辨識為DT_STR, 65001. 問題是從應該是 65001 的表中讀取的 Lookup 小元件，不知何故以 1252 告終。如果我不使用 OLE，而是從 UTF8 平面文件中讀取來創建 Lookup 小元件，則沒有錯誤。

我在閱讀整理文件時發現了這些資訊：

例如，對於OS 區域設置 “English (United States)"（程式碼頁 1252），設置期間的預設排序規則是 SQL_Latin1_General_CP1_CI_AS，它可以更改為最接近的 Windows 排序規則對應項 Latin1_General_100_CI_AS_SC。

參考： 排序規則和 Unicode 支持（Microsoft | SQL Docs）

…因此排序規則 Latin1_General_100_CI_AS_SC 確實與 Windows 程式碼頁 1252 相關…

SQL Server 2012 (11.x) 引入了一個新的補充字元 (_SC)排序規則係列，可與nchar、nvarchar和sql_variant數據類型一起使用，以表示完整的 Unicode 字元範圍 (000000–10FFFF)。例如：Latin1_General_100_CI_AS_SC或者，如果您使用的是日語排序規則，則 Japanese_Bushu_Kakusu_100_CI_AS_SC。

參考： 排序規則和 Unicode 支持 - 補充字元(Microsoft | SQL Docs)

…現在我知道 _SC 來自哪裡並繼續閱讀…

SQL Server 2019 (15.x) 引入了對廣泛使用的 UTF-8 字元編碼作為導入或導出編碼以及作為字元串數據的數據庫級或列級排序規則的完全支持。 在char和varchar數據類型****中允許使用 UTF-8，並且在您創建對象的排序規則或將其更改為具有 UTF8 後綴的排序規則時啟用它。一個範例是將 LATIN1_GENERAL_100_CI_AS_SC 更改為LATIN1_GENERAL_100_CI_AS_SC_UTF8。

參考： 排序規則和 Unicode 支持 - UTF-8 支持（Microsoft | SQL Docs）

…現在我們有一個匹配的排序規則：LATIN1_GENERAL_100_CI_AS_SC_UTF8。

總而言之，Windows 程式碼頁為 1252，SQL Server 排序規則為 UTF-8。

你的修復

破解 SSIS 包似乎可以修復對平面文件的錯誤解釋，但只有在再次編輯/修改包之前。

會不會是 TXT 文件沒有被解釋為 100% UTF-8？

TXT 文件和 UTF-8

我將此答案的前幾行保存到 .txt 文件中，並查看了 Hex-Editor 中的內容：

記事本 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

那裡似乎沒有 UTF-8 BOM 標頭。

記事本 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

因此，使用 Windows NOTEPAD.EXE 時，ANSI 和 UTF-8 的儲存方式相同。

我確實知道 UTF-8 文件有時帶有 BOM（字節順序標記），專門將文件表示為 UTF-8。

這看起來像這樣：

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

這基本上是在喊：如果你不知道，我是一個 UTF-8 編碼的文件……

但 …

UTF-8 BOM 是文本流開頭的字節序列（0xEF、0xBB、0xBF），它允許讀者更可靠地猜測文件是用 UTF-8 編碼的。

$$ … $$ 通常，BOM 用於表示編碼的字節順序，但由於字節順序與 UTF-8 無關，因此不需要 BOM。

參考： 字節順序標記（維基百科）

可能的結論

回到介紹中引用的資訊……

…SQL Server 2019 (15.x) 引入了對廣泛使用的 UTF-8 字元編碼作為導入或導出編碼以及作為字元串數據的數據庫級或列級排序規則的全面支持。允許使用 UTF-8 ….

全面支持廣泛使用的 UTF-8 字元編碼的定義是什麼？有或沒有 BOM？我們不知道，所以讓我們嘗試另一種方法。

可能的解決方案

我建議您使用支持將 TXT 文件保存為 UTF-8 並插入 BOM 的工具。

這樣，您可以確保 SSIS 包將 TXT 文件正確解釋為 UTF-8，並且不會將包轉換為解釋程式碼頁 1252。這樣，您的 SSIS 包不會遇到任何問題。

免責聲明我是一個“愚蠢的美國人”，他不處理非英語數據，但最近與朋友一起使用批量導入 UTF-8 數據，這就是我所看到的。

我有一個管道分隔值文件，看起來像這樣

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

Notepad++ 表示我已將其保存為 UTF-8。

我在 SSIS 中創建了兩個平面文件連接管理器：Codepage65001STR 和 Codepage65001WSTR。他們都使用 65001 (UTF-8) 的程式碼頁

在此處輸入圖像描述

在 STR 變體的高級選項卡中，我將數據類型保留為 DT_STR 在此處輸入圖像描述

在 WSTR 變體的高級選項卡中，我將數據類型更改為 DT_WSTR

在此處輸入圖像描述

我還創建了一個表並用相同的數據載入它

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
);

然後，我使用不同的平面文件連接管理器創建了一個帶有平面文件源的數據流任務，並在它們之間添加了數據查看器和一個空的派生列（所以我有一個數據查看器的錨點）。

我對指向我的表的 OLE DB 源以及自定義查詢做了同樣的事情

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

以及明確定義排序規則，因為它在 SSIS 中沒有什麼不同

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

結果都顯示相同，最後一個詞是帶重音的 Sante。如果 UTF-8 沒有發生，它會顯示為Santé

在此處輸入圖像描述

此時，無論我們在平面文件源列定義中是 DT_STR 還是 DT_WSTR，組件都理解 UTF-8 和 UTF-16。

屬性，每個的元數據。程式碼頁 65001 STR 看起來與預期的一樣。程式碼頁 65001 和數據類型 DT_STR

在此處輸入圖像描述

Unicode，DT_WSTR 看起來不錯

在此處輸入圖像描述

然而，OLE 組件是不同的動物。該組件返回 DT_WSTR 的元數據（完整的 Unicode/UTF-16），無論我們是否對 DT_STR 進行顯式轉換、可選地指定排序規則，或者讓自然元數據流過。

無論哪種方式，它都不會檢測到程式碼頁/排序規則的東西，只是說不，你是 Unicode

在此處輸入圖像描述

因此，當我們嘗試將查找任務與 OLE DB 連接管理器一起使用時，我們可以預期並收到相同的無法在 UTF-8 字元串/varchar 和 UTF-16/nvarchar 之間進行描述的情況

在此處輸入圖像描述

錯誤表明這是真的，DT_STR 不能匹配 DT_WSTR

無法將輸入列“名稱”映射到查找列“名稱”，因為數據類型不匹配。

那我該怎麼辦？

您必須具有類型對齊才能使查找組件工作，這意味著源數據需要是 type DT_WSTR。您可以將平面文件中的數據以 Unicode 格式導入，也可以將其保留為帶有程式碼頁 65001 的字元串。如果您採用後者，則需要複製該列並使用派生列或數據轉換工作它在查找組件中。

如果您要從查找組件中提取文本，那麼它現在作為 Unicode 在您的管道中，因此您可能希望隨後將其轉換為帶有程式碼頁的字元串類型。同樣，將使用派生列或數據轉換。

在此處輸入圖像描述

SSIS OLE 組件不理解 UTF8

我們通過原始碼和查找組件看到 SSIS 會將 UTF-8 字元串視為 UTF-16，但我認為它可以很好地處理儲存到表中。沒那麼多。

我的伺服器排序規則是Latin1_General_100_CI_AI_SC_UTF8，雖然我在伺服器和表定義之間切換了重音敏感度dbo.dba_286478，但在這種情況下並不重要，因為它一直是 UTF-8。

對於我的平面文件源，我使用基於 STR 的文件，該文件具有上面顯示的元數據，並帶有黃色突出顯示。數據類型 DT_STR 的程式碼頁 65001 是我們想要的。

我添加了一個 OLE DB 目標並將其指向我的表，該表再次將“名稱”列定義為 UTF-8

name varchar(75) COLLATE Latin1_General_100_CI_AS_SC_UTF8

檢查這個錯誤！

在此處輸入圖像描述

驗證錯誤。數據流任務 OLE DB 目標

$$ 138 $$：無法處理“名稱”列，因為為其指定了多個程式碼頁（65001 和 1252）。

我們在這個數據流中只有程式碼頁 65001，然而，SSIS 空間中的某些東西在驗證期間推斷/預設為 1252 程式碼頁。

讓它發揮作用

數據流任務中的組件在建構時考慮了 OLE DB 連接。這就是查找任務支持 2005、2008 和 2008R2 的 OLE DB 連接的原因？很久以前，我知道，但後來的迭代中添加了記憶體連接管理器（又名其他任何東西）選項，因為除了 OLE 連接管理器之外還需要使用其他東西，特別是考慮到當時的推動是棄用 OLE 驅動程序。

在這種情況下，ADO.NET 連接管理器的性能略好於 OLE，這很可能是您在處理 SSIS 包中的 UTF8 數據時必須使用的。當它呈現給表時，它將隱式轉換為 UTF-16，然後 SQL Server 會將其捕捉回 UTF-8 空間（我能說得最好）。

作為參考，使用 ADO Source 將 UTF-8 數據引入管道仍將標記為 DT_WSTR/UTF-16/unicode。

在此處輸入圖像描述

但是您可以將DT_STR 程式碼頁 65001 登陸到 ADO.NET 目標，而不會出現我在 OLE DB 目標中看到的程式碼頁不匹配錯誤。

在此處輸入圖像描述

無論您如何將其帶入管道，數據庫中的數據都將顯示為 DT_WSTR。這意味著您可以定義 OLE 和 ADO 連接管理器以按原樣使用 Lookup 組件。

或者，您可以添加一個前體數據流步驟來填充記憶體連接管理器，並且只有一個 ADO.NET 連接管理器。如果您走那條路，請將 DT_WSTR 數據轉換為程式碼頁 65001 的 DT_STR 並將該數據儲存到記憶體中。

DFT - Populate Cache -> DFT - Load data

DFT - 填充記憶體

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

DFT - 載入數據

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

引用自：https://dba.stackexchange.com/questions/286478

相關問答

Sql-Server

Arabic_100_CS_AS_KS_WS_SC_UTF8 和 Latin1_General_100_CS_AS_KS_WS_SC_UTF8 有什麼區別？

  • June 17, 2021
檢視問答
Sql-Server

安裝後更改 SQL Server 2019 實例排序規則，出現新問題

  • March 3, 2022
檢視問答
Sql-Server

SSIS 檢測順序重複

  • January 8, 2022
檢視問答
Sql-Server

SQL Server 2019 - 使用 MSA 作為代理帳戶

  • September 9, 2021
檢視問答
Sql-Server

為什麼在具有越南語_CI_AI 排序規則的 SQL Server 上比較 ’tr’ 和 ’tR’ 失敗？

  • August 26, 2021
檢視問答
Sql-Server

在表中儲存日文字元

  • June 16, 2021
檢視問答