[SSIS]動態連結EXCEL資料來源 | dustin - 點部落

2009-11-13

[SSIS]動態連結EXCEL資料來源

8997
0

摘要:[SSIS]動態連結EXCEL資料來源

先解釋一下要做到的需求

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

原本已經有一個將EXCEL資料匯入到資料庫中的封裝，

因為是預算的EXCEL資料，所以現在就要匯入預測明年1月的資料

而原本在資料來源的部份是使用Excel來源，只能固定一個檔案

但是現在又必需同時匯入09跟10年的資料，

雖然說複製一份原本的再修改一下資料來源幾乎就可以了

但是覺得這樣不就每年都要再做這件事~感覺很蠢

所以就要想一個辦法能夠把所有EXCEL的檔案都匯入的方法

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

想法

第一個直覺就是想到用FOREACH去抓資料夾中EXCEL的檔案

資料夾內的檔案

FOREACH設定，在集合頁籤選檔案列舉，抓file開頭的.xls檔(這裡可以設定*字元搜尋喔)，放到參數strFile

再來就是設定DataFlowTask，這裡用指令碼元件當作資料來源，

拉進指令碼元件會先選擇「來源」、「目的地」還是「轉換」的用途

再來就是設定指令碼元件的一些屬性

傳入的參數設為用FOREACH抓出來的檔案名稱

另外要注意的是因為2008改為precompiler的模式，所以要使用的語言要先選擇好

我這裡是用VB，若要使用C#要先在這裡修改，不然等等點進編輯指令碼就來不及了

(若是要設定自己預設使用的語言也可以到「工具」->「選項」->「商業智慧設計師」(要選項若是沒看到請勾選顯示所有設定)->

「Intergration Services 設計師」->「一般-語言」調整 )

若是為來源，就要先設定輸出的欄位

點加入資料行，這裡要填上輸出欄位的名稱跟資料型態

另外要注意我選取的Out名稱，這個會是在程式中設定欄位算是做Namespace的名稱

再來就開始寫程式了，進入到VSTA的編輯畫面，在PreExecute將EXCEL的資料放到宣告的dt裡

01

Public Overrides Sub PreExecute()
02

MyBase.PreExecute()
03

'
04

' Add your code here for preprocessing or remove if not needed
05

'
06

Dim strFile As String = Variables.strFileName.Trim
07

Dim myxls As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFile + ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
08

xls_conn = New OleDb.OleDbConnection(myxls)
09

Dim strSQL As String = String.Empty
10

strSQL = "SELECT * FROM [file01$]"
11

Dim command_oledb As OleDb.OleDbCommand = New OleDb.OleDbCommand(strSQL, xls_conn)
12

Dim adap_oledb As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(command_oledb)
13

xls_conn.Open()
14

ds = New DataSet
15

adap_oledb.Fill(ds, "ReadData")
16

dt = ds.Tables("ReadData")
17

18

19

End Sub

在CreateNewOutputRows裡指定輸出各欄位的值，OutBuffer就是剛剛設定的"Out"+"Buffer"

註解上也會有解釋

01

Public Overrides Sub CreateNewOutputRows()
02

'
03

' Add rows by calling the AddRow method on the member variable named "<Output Name>Buffer".
04

' For example, call MyOutputBuffer.AddRow() if your output was named "MyOutput".
05

'
06

With OutBuffer
07

08 For Each dr As DataRow In dt.Rows
09

.AddRow()
10

.AAA = dr.Item(0).ToString
11

.BBB = dr.Item(1).ToString
12

.CCC = dr.Item(2).ToString
13

.DDD = dr.Item(3).ToString
14

Next
15

.EndOfRowset()
16

17 End With
18

End Sub

這樣做完之後，我們就有一個資料來源了，而datasource也是由strFileName這個參數傳入

這樣我們就有一個動態的資料來源了

再來就看要轉到什麼目的地的

解釋一下為什麼要使用指令碼元件

因為如果使用原本的EXCEL來源，似乎只能傳入固定的檔案名稱，沒辦法使用參數

所以才想到要用指令碼元件來做

SSIS

回首頁