SQL Server BI Step by step 2 用SSIS簡單的匯入和匯出

 Step by step 2   用SSIS簡單的匯入和匯出

一、      建立一個Integration Service項目

開啟vs.net 2005 ,”檔案”——“建立”——選擇商業智慧項目——選中模板中的Integration Service項目——輸入項目名稱

 

 

 

二、      資料庫表匯出excel

 建立好後,我們就在預設的Package包中進行設計【詳解1】。

 

 

 SSIS 設計器介面

• 建立資料來源
  首先向控制流程中添加一個控制流程組件(Data Flow Task),雙擊進入資料流.從左邊的工具箱中選OLEDB資料來源(OLE DB Source),新添加的,標示紅色的錯誤提示。雙擊設定該組件，配置其連線管理員。為建立連線管理員。

 

建立資料來源的連線管理員

• 配置OLE DB源
  選定資料庫後設定直接存取的Produt表,當然在這裡也可以通過sql語句擷取資料來源,其中可以調用預存程序,另外還可以通過變數設定的方式,可以把表或者視圖的名稱,或者sql命令直接放在變數中

 

配置好的OLE DB連接器

配置好以後，紅色的錯誤提示已經不存在了。我們再添加一個目標資料來源,我們將資料匯出成Excel資料格式,所以選擇Excel Destination,同樣,雙擊對Excel連線管理員進行配置,配置好檔案名稱和路徑(此處選擇在首行顯示列名,這樣會從第二行開始才開始顯示資料).

 

 

拖入Excel Destination組件後

•  配置Excel Destination
  直接雙擊Excel Destination組件，報錯。因為沒有把資料來源與目標源串連。

 

 

 

 

 

 

• 配置連接器,建立表

 

 

點擊從左邊切換到映射欄目，對資料流中中繼資料的列和Excel表中的列一 一映射。因為剛才是自動建立的Excel工作表,所以預設是根據名稱對應的.這樣我們就完成了對Product產品表的匯出。在右邊的解決方案中,右擊執行包或運行（F5）,可以看到綠色執行成功.

 

 

 

映射後

 

 執行成功 匯出資料

 

三、 用SQL語句查詢出的資料進行匯出
資料來源端：把OLE  DB資料來源的資料訪問模式設定為SQL命令，然後輸入查詢語句。

 

 

在Excel Destination端：設定對應的表和映射。再次執行時就會發現產生的Excel表中已經只包含了Color=’Black’的資料(注意,如果你剛才所有的資料的Excel檔案沒有刪除,你會發現這次匯出的資料是添加到了上次的資料的後面).
 

 

 四、 excel匯入資料庫表
接下來,我們再將剛才匯出的產品資料匯入Product表中.再添加Excel Source和OLE DB Destination,其實就是做和匯出相反的過程.(注意：Product表中存入新匯入的資料，要備份下資料庫喲).
現在源是：Excel Source。
目標為：OLE DB Destination。設定時注意：資料訪問模式為 [表或視圖]。

 

確定後發現有紅色錯誤提示,這是因為資料庫中Product產品是以ProductID作為主鍵標識的,所以不能夠插入,我們從映射中設定將ProdutID欄位刪除,同樣的,我們需要將rowguid欄位忽略,這兩個欄位都是資料庫中自動產生的.  

 

再次確認後會發現已經沒有錯誤,只剩下了黃色的警告,我們現在暫時不理會這個警告.我們把剛才產生的Excel檔案刪除,重新設定Excel連線管理員產生新的空Excel檔案(或者把產生的Excel中的資料刪除),然後再次運行包。
你會發現,剛才的資料匯出仍然正常,但是資料匯入卻顯示的是沒有匯入任何資料,這是因為在資料中剛才的資料匯出和匯入並沒有先後,所以他們是同步執行的,執行匯入時發現裡面的資料為空白,所以沒有匯入成功任何資料.
實驗：嘗試著把資料匯入的操作直接放在Excel Destination後面是失敗的,當Excel Destination就是資料流目的地,意味著整個流程的結束.(此時Excel Destination中只可定義一個錯誤輸出).
因此，我們再添加一個資料流程工作——資料匯出流,將第一個資料流任何指向這個(滑鼠拖拉綠色箭頭):

 

 

匯入匯出都配置好後，清空Excel資料再次運行包,怎麼還是有錯誤,”這是為什麼呢?” ,呵呵,看下面的錯誤資訊:
錯誤: 0xC0202009，位於 資料匯入, OLE DB 目標 [960]: 出現 OLE DB 錯誤。錯誤碼: 0x80040E2F。
已獲得 OLE DB 記錄。源:“Microsoft SQL Native Client” Hresult: 0x80040E2F 說明:“語句已終止。”。
已獲得 OLE DB 記錄。源:“Microsoft SQL Native Client” Hresult: 0x80040E2F 說明:“不能在具有唯一索引 'AK_Product_ProductNumber' 的對象 'Production.Product' 中插入重複鍵的行。”。

因為，在Product表中，Name,ProductNumber,rowguid欄位中的資料都具有唯一約束性！不能插入重複的紀錄。但是rowguid是資料庫自動產生，因此在映射時，忽略即可。（備忘：查看唯一索引的目的）。

Product  表

 

解決方案：我們暫時通過添加一次轉換,在剛才的資料流源和資料流目的地中間再添加一個衍生的資料行組件(Derived Column ,Updates column values using expressions).添加一個新列NewProductNumber,在Excel中的產品編號後面加xxh,組成新的產品編號,同樣我們派生出一個新的產品名稱Name,因為在資料庫中同樣也有唯一性限制式.(注意：添加的資料xxh，必須使組成新紀錄要和表中其他紀錄有區別！例如添加“1”，也許就會和表中紀錄重複，而造成匯入的立刻終止。只匯入一部分資料).

同時,我們還要修改OLE DB目標中的映射,將目標列的ProductNumber對應的輸入列ProductNumber改成剛才派生的NewProductNumber.將目標列的Name對應的輸入列Name改成剛才派生的NewName.清空Excel資料,再次運行包,都變成了綠色,執行全部成功

 

清空Excel資料,再次運行包,都變成了綠色,執行全部成功.

 

 

通過對比資料庫,確實已經成功的添加進了504行新的資料.
細心的可能會發現,控制流程中的資料匯出和匯入兩個組件其實是前後約束條件的,也就是必須資料匯出必須成功了才會執行匯入(後面會介紹).另外,所謂的資料匯出並不會局限於資料庫的匯入和匯出,資料流源和資料流目的地都可以是Excel,Flat File(txt,csv),XML,DataReader等串連.也就是說同樣可以實現txt匯入Excel,或者是XML匯入資料庫等操作.
好了,今天是SSIS的一個入門,我們利用SSIS實現了資料的匯入和匯出,把Product表中的資料匯出成Excel,然後對產品編號和名稱兩個欄位經過派生的功能進行轉換再匯入到資料庫中,這其中我們認識了控制流程和資料流,資料流源和資料流目的地,並且還引入了衍生的資料行組件來實現我們的匯入

問題：excel匯入SQL中發現文本截斷的錯誤：

 

excel導資料到表，總是發生錯誤：資料流中Name長度為255的列中資料插入資料庫Name中長度為25的列，資料可能截斷。
這說明：是插入了重複值，違反了唯一性。
-----------------------------------------------------------錯誤報表-------------------------------------------------------------------
錯誤: 0xC0202009，位於 資料匯出, OLE DB 目標 [520]: 出現 OLE DB 錯誤。錯誤碼: 0x80040E2F。
已獲得 OLE DB 記錄。源:“Microsoft SQL Native Client” Hresult: 0x80040E2F 說明:“語句已終止。”。
已獲得 OLE DB 記錄。源:“Microsoft SQL Native Client” Hresult: 0x80040E2F 說明:“不能在具有唯一索引 'AK_Product_ProductNumber' 的對象 'Production.Product' 中插入重複鍵的行。”
--------------------------------------------------------------END-------------------------------------------------------------------------

 

 

項目step1---4原始碼檔案：版本為SQL 2005，運行代碼前還需要安裝ExceL應用程式

/Files/cocole/Step1-4Sql05.rar

 

作者：悟空的天空（天馬行空）
出處：http://www.cnblogs.com/cocole/
本文著作權歸作者和部落格園共有，歡迎轉載，但未經作者同意必須保留此段聲明，且在文章頁面明顯位置給出原文串連，否則保留追究法律責任的權利。