德瑞克：SQL Server 學習筆記: SQL Server 2008：SSIS 2008 與 Excel 2007(*.xlsx、*.xlsb)

在使用 Excel 2003(*.xls)時，有數項條件限制，例如：

* 最大資料列筆數是 65,536 列。Excel 匯出選項會檢查此數值，若超過此限制，會在 Excel 中顯示錯誤。
* 最大資料行數是 256 行。Excel 匯出選項會檢查此數值，若超過此限制，會在 Excel 中顯示錯誤。

要解決此問題，可能有幾個方法：

* 當資料筆數會超過 65,536 筆數時，採用新增工作表的方式，讓每個工作表的資料列筆數，不會超過 65,536 筆數的軟體限制。
* 改用 Excel 2007 ，存檔成 *.xlsx 的檔案格式，也可以解決此問題。
將資料匯出成 *.csv 檔案格式，分隔符號選擇為：[逗號{,}]，再利用 Excel 2007 開啟存取，也可以正常運作。

改用新版本的 Excel 2007(*.xlsx) 也可以改善這些問題，舉例來說：

* 可支援到 1,048,576 筆資料列。
* 可支援到 16,384 資料行。
* Excel 2007 讓試算表的容量擴充到超過 1 百萬資料列和 1 萬 6 千資料行。

可以使用兩種方式來將資料轉換為 Excel 2007(*.xlsx、*.xlsb)
一、使用「匯入和匯出資料 (32 位元)」，也就是「SQL Server 匯入和匯出精靈」。
二、使用 BIDS(SQL Server Business Intelligence Development Studio) 的封裝設計師。

注意事項：
Excel 2007 並沒有支援 SQL Server 2008 全部的資料類型，請記得設定合適的資料類型。
異質資料庫的資料轉換作業，資料類型的整併，也是一項需要注意的課題。

本文以使用「匯入和匯出資料 (32 位元)」，也就是「匯入匯出精靈」來做討論：

一、Excel 2007 當做資料目的地
步驟如下：
1. 執行「SQL Server 匯入和匯出精靈」。
2. 在「選擇資料來源」視窗上，本次範例使用以下的選項：
在「資料來源」方塊，選擇：「SQL Server Native Client 10.0」。
在「伺服器名稱」方塊，選擇指定的伺服器。
在「資料庫」方塊，選擇：Northwind。
點選「下一步」。

3. 在「選擇目的地」視窗上，使用以下的選項：
在「目的地」方塊，選擇：「Microsoft Office 12.0 Access Database Engine OLE DB Provider」。
點選「屬性」。

4. 在「資料連結內容」視窗，使用以下的選項：
在「連線」頁面，在「資料來源」方塊，輸入存放此 Excel 2007 檔案的路徑，例如：C:\myOrders.xlsx。
點選「全部」頁面，先點選「Extended Properties」方塊(有些會翻譯為：擴充屬性)，點選「編輯內容值」，請依據需求填入以下的任一值：

若 Excel 2007 的附檔名為：xlsx 時，請填入：Excel 12.0 Xml 。
若 Excel 2007 的附檔名為：xlsb 時，請填入：Excel 12.0 。

假使填寫錯誤，雖然資料能夠成功匯出，但卻無法開啟。
經過測試，請自行修改為正確的附檔名後，就可以開啟。
5. 點選「確定」，完成「選擇目的地」視窗的設定，再點選「下一步」。r
6. 在「指定資料表複製或查詢」視窗，點選「從一或多個資料表或檢視表複製資料」方塊。

7. 點選「下一步」。
8. 在「選取來源資料和檢視」視窗，勾選所需要的資料表和檢視，例如：勾選[dbo].[Orders]。

9. 點選「下一步」。
10. 在「檢閱資料類型對應」視窗，使用以下的選項：
在「資料類型對應」區域，建議取消在「轉換」方塊下，所勾選的部份。

雖然「SQL Server 匯入和匯出精靈」能自動偵測是否需要執行轉換資料類型。
但在 SQL Server 2008 SP1 版本上，這項功能可能會有問題。

11. 點選「下一步」。
12. 在「儲存並執行封裝」視窗，點選「立即執行」方塊。

13. 點選「下一步」。
14. 在「完成精靈」視窗，點選「完成」。

15. 在「已成功執行」視窗，點選「關閉」。

完成本次實作練習。

以下為將此次轉換作業存檔為封裝後，觀察有使用自動轉換與取消自動轉換兩者之差異：
(A) 使用預設的自動轉換
SSIS 2008 會自動轉型為：DT_NTEXT。

(B) 取消自動轉換
使用的資料類型為：DT_WSTR。

有關於Integration Services 資料類型的說明：
DT_WSTR：最大長度為 4000 字元，以 Null 終止的 Unicode 字元字串。如果資料行值包含額外的 Null 結束字元，字串就會在第一個 Null 出現時被截斷。

DT_NTEXT：最大長度為 230 - 1 (1,073,741,823) 個字元的 Unicode 字元字串。

以下是摘錄 SQL Server 2008 線上說明，有關於「Excel 目的地」的說明：

正在儲存備忘 (ntext) 資料。在 Excel 資料行中成功儲存長於 255 個字元的字串之前，驅動程式必須能將目的地資料行的資料類型辨識為備忘，而不是字串。

如果目的地資料表已包含資料列，則驅動程式所取樣的前幾個資料列必須在備忘資料行中至少包含一個值長於 255 個字元的執行個體。

如果目的地資料表是在封裝設計期間或在執行階段建立，則 CREATE TABLE 陳述式必須使用 LONGTEXT (或其同義字之一) 做為備忘資料行的資料類型。
...

Integration Services 對應 Excel 資料類型的情況如下：
...
字串 – Unicode 字串，長度 255 (DT_WSTR)
備忘錄 – Unicode 文字資料流 (DT_NTEXT)
...

資料來源。Excel 活頁簿中資料的來源可以是工作表 (必須附加 $ 符號，例如 Sheet1$) 或已命名的範圍 (例如 MyRange)。

在 SQL 陳述式中，工作表的名稱必須加以分隔 (例如 [Sheet1$])，以避免 $ 符號造成的語法錯誤。

「查詢產生器」會自動加入這些分隔符號。
當您指定工作表或範圍時，驅動程式會讀取連續的資料格區塊，從工作表或範圍左上角的第一個非空白資料格開始。

因此，來源資料的資料列不可以空白，或標題或標頭資料列與資料列之間不可以有空白資料列。
...
遺漏值。Excel 驅動程式會在指定來源中讀取特定資料列數目 (依預設為 8 個資料列)，以猜測各資料行的資料類型。
...
截斷的文字。當驅動程式判斷出某個 Excel 資料行包含文字資料時，驅動程式將會根據其取樣的最長值來選取資料類型 (字串或備忘錄)。

如果驅動程式未在其取樣的資料列中發現任何長度超過 255 個字元的值，則會將該資料行視為 255 個字元字串資料行而非備忘錄資料行因此，長度超過 255 個字元的值可能會被截斷。

若要以不截斷的方式從備忘資料行匯入資料，您必須確保至少在其中一個取樣資料列中的備忘錄資料行包含長度超過 255 個字元的值，否則您就必須增加驅動程式取樣的資料列數目，使其包含這類資料列。

您可以提高 HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel 登錄機碼下 TypeGuessRows 的值，以增加取樣的資料列數目。

由上，雖然使用 Excel 2007(*.xlsx、*.xlsb) 與 SSIS 2008 可以超過 65,536 列的限制。
但經過測試並與 Microsoft 確認後：
Excel 2007(*.xlsx、*.xlsb) 所使用的 Microsoft Office 12.0 Access 資料庫引擎 OLE DB 提供者，簡稱為：ACE Provider 可能有無法正確支援 DT_NTEXT 的問題，也就是無法支援超過 255 個字元的資料。這個問題在Excel 2010會修復. 目前的workaround就是使用 excel 2003了….

在此感謝 Vera 熱心的協助。

有關於 Excel 2007 附檔名的說明：
xlsx ：此為預設的 Office Excel 2007 XML 檔案格式。
xlsb ：Office Excel 2007 二進位檔案格式 (BIFF12)。

更新日期：2010/06/26

經過測試，在 Excel 2010、SQL Server 2008 R2 中文版本上，仍是有此問題。
「Microsoft Office 12.0 Access Database Engine OLE DB Provider」，簡稱為：ACE Provider 可能有無法正確支援 DT_NTEXT 的問題。

請參考下圖，仍看不到 Excel 2010 的標籤。