SSIS高級轉換任務—導入列

在SSIS高級轉換任務—導出列這一篇隨筆中主要完成的是把數據庫中的文件列導出，這里將討論如何將文件導入到數據庫中，它和導出列任務是一對經常搭配使用的任務。當我們搞清楚它們實現什么功能的時候會發現原來的名字更加貼切。這種轉換將系統文件路徑中的物理文件轉化為數據庫中的表數據，反之亦然。理解輸入列任務的關鍵是輸入源中至少有一列保存將要導入到數據庫中的文件的路徑，還需要目標列保存文件和文件路徑。這種轉換仍然使用高級編輯。

就因為它是高級編輯所以不夠直觀，也沒有類似于向導一樣的設置，但是熟悉之后使用它將會很方便。在高級編輯中沒有類似將兩列合并成一列這樣的設置所以如果源數據中文件路徑和文件名字是分離的，需要使用Merge轉換將他們連接起來。

導入列例子

現在來做一個例子將圖像文件導入到AdventureWorks數據庫中。新建一個包命名為ImportColumnExample,添加一個Data Flow Task，在Data Flow界面拖放一個Import Column transformation,按照下面步驟設置包：

1. 創建路徑C:\Users \Pictures\MyPicture
2. 隨便在路徑C:\Users\Pictures\MyPicture中粘貼3個比較小的圖像文件命名為1.jpg，2.jpg，3.jpg
3. 在路徑C:\Users\Pictures\MyPicture內創建一個txt文件命名為filelist.txt，文件內容如下
   

   ImageFilePath
   C:\Users \Pictures\MyPicture\1.jpg
   C:\Users \Pictures\MyPicture\2.jpg
   C:\Users \Pictures\MyPicture\3.jpg

4. 運行下面的sql語句創建表 
   

   use AdventureWorks
   Go
   CREATE TABLE dbo.tblmyImages
   (
       [StoredFilePath] [varchar](50) NOT NULL,
       [Document] image
   )

5. 使用filelist.txt作為要載入到數據庫中的文件的流，添加一個Flat File Source，filelist.txt作為數據源。右擊Flat File Source，選擇Show Advanced Editor，它和直接雙擊打開的編輯界面有所不同，沒有設置向導，直接對屬性進行設置。可能覺得一團亂麻不好辨別，但是在最糟糕的情況下你可以直接刪除重新設置。高級編輯界面如下圖1：
   圖1
6. 在這個例子中，Flat File Source的高級編輯界面和Import Column Transormation的高級編輯界面很類似。在Import and Output Properties標簽界面有兩個文件夾結點External Columns和Output Columns，他們都有一個子結點ImageFilePath，這個Flat File Source要輸出的數據列名和數據源中的列名一致都是ImageFilePath我們還會看到屬性ID是16，ExternalMetaDataColumnID是15。這表示這個轉換任務將源輸入和輸出連接起來。我們注意到下面有Add Output和Remove Output兩個按鈕但是在這個任務中不能配置這個選項。這里他的作用是將文件中的字符轉換成字符流。Column Mappings標簽內顯示映射關系如圖2，如果將filelist.txt內的列名改為myImageFilePath那么這里也將顯示為myImageFilePath。
   
   圖2  這里TxtFilePath是我自己后來添加的另外一個文本文件列。
7. 將Flat File Source和Import Column連接起來。打開Import Column任務的高級編輯選項，選擇Input Column標簽，它的輸入流是Flat File Source的輸出流，點擊選中ImageFilePath，切換到Input and Output Propertitis界面，如圖6-6，輸入列集合中有一列ImageFilePath，但是在輸出列集合中沒有。在Flat File Source可以忽略輸入，但是在這里所有的輸入都需要有對應輸出，事實上如果沒有輸出會有下面的錯誤提示：
   Validation error. Data Flow Task: Import Column [1]: The "input column "ImageFilePath" (164)" references output column ID 0, and that column is not found
8. 選中Output Columns文件夾，點擊Add Column按鈕添加新列命名為myimage。注意到DataType屬性是image [DT_IMAGE]，這是因為輸入流中是圖像文件，這里夜課儀選擇DT_TEXT，DT_NTEXT或者DT_IMAGE。最后需要將輸入和輸出連接起來，這里注意到myImage列的ID屬性是77，這里需要將Input Column集合中ImageFilePath的FileDataColumnID屬性設置為77，否則可能如下的錯誤提示：
   Validation error. Data Flow Task: Import Column [1]: The "output column "myImage" (207)" is not referenced by any input column. Each output column must be referenced by exactly one input column.意思是myImage沒有被input column引用到，每一個輸出必須有一個對應輸入。這里注意如果有多個列，還應設置Input Columns中對應的MappedColumnID屬性。
9. 最后添加OLE DB Desination，將Import Column和Ole DB Destination連接起來，編輯Ole DB Destination設置為如上創建的表，點擊Mappings setting設置列映射最后運行包，界面如下圖3：
   
   圖3

最后，數據庫中目標表的數據如下：

FullFileName               Document

----------------------     -----------------------------------

C:\import\images\1.JPG     0xFFD8FFE120EE45786966000049492A00...

C:\import\images\2.JPG     0xFFD8FFE125FE45786966000049492A00...

C:\import\images\3.JPG     0xFFD8FFE1269B45786966000049492A00...

(3 row(s) affected)

使用循環導入列

在現實環境中可能很少遇到上面的情況，可能要將一系列文件導入到數據庫中，這些文件的路徑整齊到排放在一個文本文件中。實際情況是從一個FTP文件夾，剪切板中的文件夾中獲得文件，將文件載入到數據庫中。這里需要自己構件文件路徑。可以使用Foreach Loop Container來檢索文件，關鍵的地方是找到可以傳遞文件路徑的字符流。Foreach Loop Container可以得到一系列整齊的文件，但是不能轉化成數據源，可以使用sql語句來產生整齊的文件路徑，但是這里有更好的方法，讀取文件夾內的文件將文件路徑保存在數據庫中。然后使用Ole DB Source來訪問數據并將文件保存在數據庫中。

1. 使用下面的語句在數據庫中創建一個表USE AdventureWorks
   GO
   CREATE TABLE stgfilelist
   (
      [FullFileName] [varchar](50) NOT NULL,
   )
2. 在Control Flow區域中拖放一個Foreach Loop Container，Foreach container enumerator屬性設置為Foreach File Enumerator，Folder屬性設置為C:\Users \Pictures\MyPicture，Files屬性設置為.jpg，設置Retrieve File Name屬性為Fully qualified
3. 點擊Variable Mappings，創建一個string類型的package范圍內的變量，修改名字為myFilePath，保持Index為默認值0，這將路徑內所有文件的文件名保存到數據庫中，如圖4
   
   圖4
4. 在Foreach Loop container中拖放一個Execute SQL，設置Connection屬性為數據庫Adventure
5. 點擊Expression選項，找到SQLStatementSource屬性，編寫一個sql語句將當前變量myFilePath檢索的文件的路徑寫入到數據庫中。Expression表達式如下，使用雙引號包括起來："INSERT INTO stgFileList SELECT '"+ @[User::myFilePath] + "'"
6. 將Foreach Loop Container和上次練習中的Data Flow task連接起來，這里直接使用，不再贅述了。
7. 再在整個任務上面添加一個Execute Sql task，刪除歷史記錄，語句如下
   

   IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[stgfilelist]') AND type in (N'U'))
   BEGIN
   TRUNCATE TABLE dbo.stgfilelist
   END
   IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblmyMp3]') AND type in (N'U'))
   BEGIN
   TRUNCATE TABLE dbo.tblmyMp3
   END

8. 這里注意到Import Column有一個錯誤，這是因為修改了數據源錯誤提示如下：Validation error. Data Flow Task: DTS.Pipeline: input column "FullFileName" (336)has lineage ID 319 that was not previously used in the Data Flow task
9. 打開Import Column的高級編輯界面重新編輯，將會看到無效的列參考信息如圖5，這里可以選擇無效映射并刪除，重新選擇OLE DB Source作為源。每一列有單獨的ID。這里列名類似在Columns中的下拉列表框中設置新的列，最后保存，打開OLE DB Destination保證數據正確地映射到數據表中。
   
   圖5
10. 執行包查看結果文件被保存到數據庫中的tblmyImages表中，路徑信息保存到stgfilelist表中。最后結果如圖6
    
    圖6