搜尋本站文章

2011-10-27

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



參考資料

Excel 目的地
http://msdn.microsoft.com/zh-tw/library/ms137643.aspx

無法匯入超過 65,536 列是文字檔到 Excel 97、 Excel 2000、 Excel 2002 和 Excel 2003
http://support.microsoft.com/kb/120596/zh-tw

SQL Server 2008:SSIS 2008 與 Excel 2007(*.xlsx、*.xlsb)
http://sharedderrick.blogspot.com/2009/11/sql-server-2008ssis-2008-excel.html

SSIS 2005 與 Excel 2007 (*.xlsx , *.xlsb)
http://sharedderrick.blogspot.com/2008/01/ssis-excel-2007-xlsx-xlsb.html

SSIS 2005 與 Access 2007 (*.accdb)
http://sharedderrick.blogspot.com/2008/05/ssis-access-2007.html