德瑞克：SQL Server 學習筆記: SSIS：使用 Excel 2010 (例如：附檔名為 xlsx)為來源或是目的地

示範環境：
SQL Server 2008 R2 x64 位元。

實作練習：
SSIS 使用 Excel 2010 (例如：附檔名為 xlsx)為來源或是目的地

準備工作：
請事先安裝好 Microsoft Office Access Database Engine OLE DB Provider 驅動程式

工作1：使用 Excel 2010 當做資料來源

步驟01. 在「資料流程」頁面，在左邊的工具箱，在「資料流程來源」區域，選擇「OLE DB 來源」，拖曳到封裝設計頁面。

步驟02. 選取此「OLE DB 來源」，滑鼠右鍵，選擇「編輯」。

步驟03. 在「OLE DB 來源編輯器」視窗，設定以下的參數：

在左邊窗格，點選「連接管理員」，在右邊窗格，在「OLE DB 連接管理員」區域，點選「新增」。

在「設定 OLE DB 連接管理員」視窗，點選「新增」。

在「連接管理員」視窗，在「提供者」區域，下拉選擇：「原生 OLE DB\Microsoft Office 12.0 Access Database Engine OLE DB Provider」。

-- 01_使用 ACE 12：Microsoft Office 12.0 Access Database Engine OLE DB Provider

-- 01_US_選擇ACE12

在「OLE DB 提供者」區域，點選「資料連結」。

在「資料連結內容」視窗，在「連線」頁面，在「資料來源」方塊，輸入 Excel 2010 來源檔案的路徑與檔案名，例如：C:\mySSIS\myTransactionHistory.xlsx。

-- 02_輸入資料來源

點選「全部」頁籤，在「名稱」區域，點選「Extended Properties」屬性，點選「編輯內容值」。

在「編輯內容值」視窗，在「內容值」方塊，輸入：Excel 12.0 Xml，點選「確定」。

-- 03_編輯內容值_填入Excel 12.0 Xml

-- 04_設定Extended Properties屬性值

在「資料連結內容」視窗，點選「確定」。

在「連接管理員」視窗，點選「測試連接」，在「連接管理員」視窗之連接測試成功訊息方塊，點選「確定」\「確定」。

-- 05_完成_驅動程式的相關屬性設定

-- 05_US_完成_驅動程式的設定

-- 05_CHT_1_檢視進階屬性

-- 05_US_1_檢視進階屬性

在「設定 OLE DB 連接管理員」視窗，點選「確定」。

-- 06_選擇已經設定好的資料連接

在「OLE DB 來源編輯器」視窗，在「資料表或檢視表的名稱」方塊，下拉選擇指定的 Excel 頁籤名稱，例如：TransactionHistory$。

附註：

1. 「工作表(worksheet )」，例如：Sheet1$，以附加到工作表名稱的 $ 符號識別。
2. 「具名範圍(named ranges)」，例如：MyRange，以沒有 $ 符號的方式識別

-- 07_選擇資料表或檢視表的名稱

點選「預覽」，在「預覽查詢結果」視窗，點選「關閉」。

在「OLE DB 來源編輯器」視窗，點選「確定」。

工作2：使用 Excel 2010 當做資料目的地

步驟01. 在「資料流程」頁面，在左邊的工具箱，在「資料流程目的地」區域，選擇「OLE DB 目的地」，拖曳到封裝設計頁面。

步驟02. 點選先前建立的「OLE DB 來源」，拖曳綠色線路的「資料流程」到「OLE DB 目的地」。

步驟03. 選取此「OLE DB 目的地」，滑鼠右鍵，選擇「編輯」。

步驟04. 在「OLE DB 目的地編輯器」視窗，設定以下的參數：

在左邊窗格，點選「連接管理員」，在右邊窗格，在「OLE DB 連接管理員」區域，點選「新增」。

在「設定 OLE DB 連接管理員」視窗，點選「新增」。

在「連接管理員」視窗，在「提供者」區域，下拉選擇：「原生 OLE DB\Microsoft Office 12.0 Access Database Engine OLE DB Provider」。

在「OLE DB 提供者」區域，點選「資料連結」。

在「資料連結內容」視窗，在「連線」頁面，在「資料來源」方塊，輸入 Excel 2010 來源檔案的路徑與檔案名，例如：C:\mySSIS\myTransactionHistory_Out.xlsx。

點選「全部」頁籤，在「名稱」區域，點選「Extended Properties」屬性，點選「編輯內容值」。

在「編輯內容值」視窗，在「內容值」方塊，輸入：Excel 12.0 Xml，點選「確定」。

在「資料連結內容」視窗，點選「確定」。

在「連接管理員」視窗，點選「測試連接」，在「連接管理員」視窗之連接測試成功訊息方塊，點選「確定」\「確定」。

在「設定 OLE DB 連接管理員」視窗，點選「確定」。

在「OLE DB 目的地編輯器」視窗，點選「新增」。

在訊息視窗：「將 SSIS 類型對應到所選 .NET 資料提供者的資料類型的相關資訊不足。因此，您可能需要在下一個畫面中修改 SQL 陳述式的預設資料行名稱」，點選「確定」。

-- 08_在訊息視窗

在「建立資料表」視窗，修改其所建立的 Excel 頁籤名稱，例如：TransactionHistory (原本是 OLE DB 目的地)，點選「確定」。

-- 09_在「建立資料表」視窗

在「資料表或檢視表」方塊，下拉選取：TransactionHistory$。

-- 10_在「資料表或檢視表」方塊

在左邊窗格，點選「對應」，確認輸入與目的地之資料行，點選「對應」，點選「確定」。

工作3：檢視執行成果

步驟01. 執行此封裝。

-- 11_執行此封裝

若是沒有安裝 Microsoft Office Access Database Engine OLE DB Provider

將遭遇以下的錯誤訊息：

SQL Server Business Intelligence Development Studio 的執行記錄：

[連接管理員 "C:\mySSIS\myTransactionHistory.xlsx"] 錯誤: SSIS 錯誤碼 DTS_E_OLEDB_NOPROVIDER_64BIT_ERROR。
要求的 OLE DB 提供者 Microsoft.ACE.OLEDB.12.0 並未註冊 -- 可能是沒有 64 位元提供者可用。錯誤碼: 0x00000000。
有 OLE DB 記錄可用。來源: "Microsoft OLE DB Service Components"  Hresult: 0x80040154  描述: "類別未登錄"。

「作業」的紀錄：

訊息
以下列使用者的身分執行: xxx\Administrator。
Microsoft (R) SQL Server 執行封裝公用程式  Version 10.50.2500.0 for 64-bit  Copyright (C) Microsoft Corporation 2010. All rights reserved.    
已啟動:  18:08:30  錯誤: 2011-10-27 18:08:30.92     代碼: 0xC0209303     來源: Excel_2010_ACE12 連接管理員 "C:\mySSIS\myTransactionHistory.xlsx"     描述: SSIS 錯誤碼 DTS_E_OLEDB_NOPROVIDER_64BIT_ERROR。
要求的 OLE DB 提供者 Microsoft.ACE.OLEDB.12.0 並未註冊 -- 可能是沒有 64 位元提供者可用。
錯誤碼: 0x00000000。  有 OLE DB 記錄可用。來源: "Microsoft OLE DB Service Components"  Hresult: 0x80040154  描述: "類別未登錄"。  
錯誤結束  錯誤: 2011-10-27 18:08:30.93     代碼: 0xC020801C     來源: 資料流程工作 OLE DB 來源 [1]     描述: SSIS 錯誤碼 DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER。對 "C:\mySSIS\myTransactionHistory.xlsx" 連接管理員呼叫 AcquireConnection 方法失敗，錯誤碼為 0xC0209303。
在此之前可能已公佈過錯誤訊息，說明 AcquireConnection 方法呼叫為何失敗的詳細資訊。  
錯誤結束  錯誤: 2011-10-27 18:08:30.93     代碼: 0xC0047017     來源: 資料流程工作 SSIS.Pipeline     描述: 元件 "OLE DB 來源" (1) 驗證失敗，傳回錯誤碼 0xC020801C。  錯誤結束  錯誤: 2011-10-27 18:08:30.93     代碼: 0xC004700C     來源: 資料流程工作 SSIS.Pipeline     描述: 一個或多個元件驗證失敗。 
 錯誤結束  錯誤: 2011-10-27 18:08:30.93     代碼: 0xC0024107     來源: 資料流程工作      描述: 工作驗證期間發生錯誤。  錯誤結束  DTExec: 封裝執行傳回 DTSER_FAILURE (1)。  
已啟動:  18:08:30  已完成: 18:08:30  經過時間:  0.125 秒.  封裝執行失敗。.  步驟失敗。

-- 12_錯誤訊息_作業_記錄

-- 13_錯誤訊息_手動執行封裝

-- 14_錯誤訊息_手動執行封裝

認識 Excel 的「工作表(worksheet )」與「具名範圍(named ranges)」

在 Excel 中，工作表或範圍相當於資料表或檢視。
「Excel 來源」及「Excel 目的地」編輯器中的可用資料表清單，僅會顯示現有工作表 (以附加到工作表名稱的 $ 符號識別，例如 Sheet1$) 及具名範圍 (以沒有 $ 符號的方式識別，例如 MyRange)。

整理如下：

1. 「工作表(worksheet )」，例如：Sheet1$，以附加到工作表名稱的 $ 符號識別。

2. 「具名範圍(named ranges)」，例如：MyRange，以沒有 $ 符號的方式識別

下載 Microsoft Office Access Database Engine OLE DB Provider

2007 Office system 驅動程式：資料連線元件
http://www.microsoft.com/downloads/details.aspx?displaylang=zh-tw&FamilyID=7554f536-8c28-4598-9b72-ef94e038c891

Microsoft Access Database Engine 2010 可轉散發套件
http://www.microsoft.com/downloads/details.aspx?familyid=C06B8369-60DD-4B64-A44B-84B371EDE16D&displaylang=zh-tw