SSIS功能體驗(一)、匯入EXCEL檔案

撰文：吳俊毅
時間：2010/02/03

關於SSIS的主題筆者早就躍躍欲試了，只是礙於沒有機會接觸，相信MS SQL 2005的SSIS許多人早已體驗過其強大的功能，與MS SQL 2000的DTS相比強大好幾倍，SSIS加上對.NET Framework平台的支援，指令碼的處理的Script可不像DTS只可以使用Active JScript/VBScript做為主要語言，SSIS可以支援以C#來撰寫指令碼的處理。筆者因為工作的關係需要將EXCEL檔案大量且批次的匯入到資料庫中，因為EXCEL格式為固定，而且每天都需要匯入，如果要寫程式匯入當然也可以，但未免過於麻煩，因為要寫的程式太多了，能不要寫當然就不要寫！所以筆者就想到SSIS這個強大的功能。OK我們一步步來進行！

首先需要使用VS 2008開一個新的Integration Services 專案，如下圖(屬於商業智慧專案)：


新的專案預設會切在控制流程的畫面，如下圖：


對SSIS而言所有的作業基礎即是控制流程，(眼尖的讀者會發現當切換控制流程與資料流程頁籤時左邊TOOLBAR的元件是不一樣的！)接下來拖曳一個資料流程工作至畫面中，並在該資料流程工作上點選滑鼠右鍵選擇編輯後畫面會自動切到資料流程頁籤，然後將"Excel來源" 元件拖曳至畫面中，畫面如下：


接著在Excel來源點選右鍵編輯會切到EXECL來源編輯視窗，並新增一個Excel連接管理員，再設定Excel的檔案路徑，如下圖設定畫面：


以上動作都完成且確認無誤後將Excel來源編輯器的資料存取模式設定為SQL指令方式，點選建立查詢按鈕可以SQL Statement方式將Sheet1$的資料Select進來，對SSIS而言EXCEL的Sheet即是一個Table，這個概念不是到了SQL 2005才有的，早在DTS的時代就有這個概念，因為一個EXCEL檔案通常有三個Sheet，所以在這裡會看見三個Table，那麼因為筆者的EXCEL中只有Sheet1有資料，所以只加入Sheet1$就可以了！如下圖：


確定後我們可以Select看看，Sheet1$的資料會秀在下面：


決定好SQL按下確定後請預先在資料庫中建立一個Table以便匯入使用
CREATE TABLE [dbo].[Tb_Sales](
[ID] [int] NULL,
[EN_NAME] [nvarchar](30) NULL,
[CHT_NAME] [nvarchar](50) NULL,
[DESC] [ntext] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

讀者可以在Excel來源編輯器查看資料行頁籤是否正確，然後再拖曳一個SQL Server目的地元件至資料流程畫面中，並將綠色的連接線連結至該SQL Server目的的元件，如下圖：


並同樣點選右鍵編輯，先加入連接管理員指定要匯入的資料庫以及資料表，該資料表當然就是剛剛預先建立的資料表Tb_Sales，如下圖：


接著點選對應頁籤，請手動的拖曳的方式將序號對應到ID、英文姓名對應到EN_NAME、中文姓名對應到CHT_NAME、備註對應到DESC，完成後如下圖：


在設定完成後回到資料流程的畫面中讀者會發現在SQL Server目的地中有一個錯誤訊息表示無法在類型DT_R8與DT_I4之間轉換，這是因為EXCEL表格中並未將儲存格的格式設定為數字的關係，但因為現實情形中USER不可能一個個在EXCEL中設定好儲存格格是給你，所以我們必須要轉換，再拖曳一個資料轉換元件至資料流程畫面中，修改連接線，先透過轉換元件才匯入到SQL Server 目的地中，接著並點選右鍵進階編輯，先將輸入資料行勾選進來，再切換到輸入與輸出的頁籤 將輸出的資料行 的 序號的副本的DataType屬性改為 四位元組帶正負號的整數 [DT_I4] ，再將英文姓名與中文姓名的副本的Data Length分別改為30與50與Tb_Sales資料表相同，最後整個畫面如下：


現在可以點選執行，執行時畫面綠色的框框選擇的畫面，表是目前執行到的位置，IDE工具並提供進階偵錯功能，更進階的部分筆者下次再介紹，執行畫面如下：


可切換到進度的檢視查看執行的LOG結果，看看是否成功！如下圖：


我們實際到TABLE中查詢看看資料是否有進到資料庫中：


果然都進到資料庫中了，這個SSIS封裝我們可以將它安裝到SQL Agent中排程執行，這個就為筆者下次的內容啦~謝謝大家