SSIS中指令碼元件的使用

by adonis 6. 二月 2013 12:12

作    者:楊先民
審    稿:張智凱

前言

SQL Server的 SSIS一直以來都是用以解決資料匯入匯出以及轉換問題的最佳工具,也能夠讓設計人員自行設計資料流與控制流以滿足各式各樣匯入匯出以及資料轉換的需求,而這次的主題將放在 SSIS中資料流裡的很重要的「指令碼元件」的使用,並且介紹何時我們會需要用到它。

資料的轉換

有學過 SSIS的人應該都知道,資料的搬移算是裡面最簡單的技術,因為資料只需要移過去即可不需要改變資料,但是如果你希望在搬移的過程中做一些改變,這將會視你要變動的內容多寡來決定到底容不容易達成。

而大多數的資料匯入匯出,或是轉換都已經存在資料來源,甚至幾乎都是 SQL Server,如果你的資料來源不一定像教科書一樣的話,有的時後你需要自己寫程式來撰寫。

「寫程式撰寫資料來源」?

沒錯!不但資料來源可以寫程式撰寫,資料目的也可以,轉換方式也可以,也就是說,我們本期的主題在「指令碼元件」的使用,它可以是你最後想盡辦法,無法可解的最後一道解決方案,如果沒有現成的控制項可以用的話,那乾脆就自己寫吧。
不過在介紹這個元件之前,還是稍微說明一下我們的情境:

資料來源是文字檔,此文字檔並非傳統的用逗號分隔的文字檔,而是固定長度的文字檔,幸好列與列之間還是用enter隔開,只有長度是固定長度,如下圖:

image

如果你要自己一個個慢慢的切的話,可能會花很久的時間,所以,這裡想到的一招是,可以利用指令碼元件來達成我們的需求。

在實作指令碼元件之前,提醒一下資料流中的指令碼元件,與控制流中的指令碼工作的程式碼撰寫方式是不同的,而本期所提及的指令碼元件是用在資料流的,不要和控制流中的指令碼工作混為一談。

首先先在控制流中加入資料流控制項,並且在 SSIS工具箱中點選指令碼元件,如下圖:

image

這時畫面會請你選擇,如何在資料流中使用指令碼,如下圖:

image

你可以用指令碼元件自己撰寫是否為來源、目的地或是轉換,由於本情境我們要自己抓txt文件檔,所以資料來源我們將要自己抓,所以這裡選的是「來源」。

如果你選擇的是資料來源,你必需要自行設定「輸出資料行」,因為來源你要自己產生,將結果丟給下一個的轉換處理(所以轉換指令碼元件將會有輸出以及輸出)。

至於如何設定輸出資料行,只需把指令碼元件雙擊兩下點來,選擇「輸入及輸出」,就可以自行設定資料表,以及資料行名稱,和資料型別,如下圖:

image

至於資料型別的部分,我統一設定為 Unicode字串,長度為 50,如下圖:

image

不過得確認你的文件檔是否為 unicode格式就是了。

接下來就可以利用「編輯指令碼」來撰寫程式了。

在程式碼中,已經有事先定義好的override函數,你只要依照自身的需求將程式寫在適當的地點即可,函數有下面幾個:
1.PreExecute:建立新資料列之前做的事情,通常是把連線開啟(如果你是使用 ado .net,通常就是把連線開始,也就是 connection open)

2.PostExecute:建立新資料列之後做的事情,通常是把連線關閉(如果你是使用 ado .net,通常就是把連線關閉,也就是 connection close)

3.CreateNewOutputRows:自行用程式產生一列資料列,放在剛才所設定的輸出格式中。

所以就本題義來看,就是要自行撰寫 CreateNewOutputRows,自行產生一列資料。

以下是我程式撰寫方式:

      StreamReader objReader = new StreamReader(@"c:\northwind.txt");
      string sLine = "";
        int nowCount=0;

        while (!objReader.EndOfStream && nowCount <= this.Variables.fileLine)
        {
            sLine = objReader.ReadLine();

            if (nowCount == this.Variables.counter)
            {
                //如果目前個數等於要取出的列

                EmployeeBuffer.AddRow();

                EmployeeBuffer.EmployeeID = sLine.Substring(0, 10);
                EmployeeBuffer.LastName = sLine.Substring(11, 20);

                EmployeeBuffer.FirstName = sLine.Substring(31, 10);
                EmployeeBuffer.Title = sLine.Substring(41, 30);
                EmployeeBuffer.TitleOfCourtesy = sLine.Substring(71, 25);

            }

            nowCount++;

        }
        objReader.Close();

這個程式要注意幾件事情,首先,我們要用
      StreamReader objReader = new StreamReader(@"c:\northwind.txt");
將放在C槽的 northwind.txt檔案取出(也就是我們事先準備的文件檔,當然這裡你也可以使用變數)。

接下來就是撰寫迴圈,將資料一列列取出,並且放在輸出的結構當中。

不過要注意的是,因為一個CreateNewOutputRows只能處理一列,所以除非你的文件只有一列,不然就必需寫成下面,我之前的寫法:

    private string pEmployeeid;
    private string pLastName;
    private string pFirstName;
    private string pTitle;
    private string pTitleOfCourtesy;
    private int intCount;

    public override void PreExecute()
    {
        base.PreExecute();
        /*
         * Add your code here
         */

        pEmployeeid = "";
        pLastName = "";
        pFirstName = "";
        pTitle = "";
        pTitleOfCourtesy = "";
        intCount = 0;

    }

    public override void CreateNewOutputRows()
    {
        StreamReader objReader = new StreamReader(@"c:\northwind.txt");
        string sLine = "";
        while (!objReader.EndOfStream)
        {
            sLine = objReader.ReadLine();

            if (intCount == 0)
            {
                pEmployeeid = sLine.Substring(0, 10);
                pLastName = sLine.Substring(11, 20);
                pFirstName = sLine.Substring(31, 10);
                pTitle = sLine.Substring(41, 30);
                pTitleOfCourtesy = sLine.Substring(71, 25);

                intCount = 1;

            }
            else
            {

                pEmployeeid += ",";
                pEmployeeid += sLine.Substring(0, 10);
                pLastName += ",";
                pLastName += sLine.Substring(11, 20);
                pFirstName += ",";
                pFirstName += sLine.Substring(31, 10);
                pTitle += ",";
                pTitle += sLine.Substring(41, 30);

                pTitleOfCourtesy += ",";
                pTitleOfCourtesy += sLine.Substring(71, 25);

            }

        }
        objReader.Close();

        EmployeeBuffer.AddRow();

        EmployeeBuffer.EmployeeID = pEmployeeid;
        EmployeeBuffer.LastName = pLastName;

        EmployeeBuffer.FirstName = pFirstName;
        EmployeeBuffer.Title = pTitle;
        EmployeeBuffer.TitleOfCourtesy = pTitleOfCourtesy;

    }
先設定private字串,把每個預期抓出的欄位先設定成 private成員,然後在CreateNewOutputRows函數中,利用 while 迴圈搭配 streamreader將文件資料列取出,並且將變數間用逗號隔開。

但是這有一個超級大的問題,就是如果文件檔很大,欄位長度鐵定不夠長,而且速度會變的非常慢,之所以附上這個錯誤寫法,是希望各位能了解不要寫錯囉!應該要改寫才是。

要怎麼改寫呢?首先我們需要將控制流做成如下的設計:

image

是的,我們要使用 For迴圈,由於 SSIS並沒有 WHILE迴圈這種東西,所以只能勉強用 For迴圈來改。
要改的話就需要先取得文件檔的總列數,在此設定兩個變數,如下:

image

counter是用在 For迴圈的變數,而 fileLine則是將抓到的文件列數丟給 fileLine變數。

但需注意,在工作流中寫的是「指令碼工作」,而在資料流中寫的是「指令碼元件」,寫法並不儘將同,指令碼工作並需先設定哪些變數可以在指令碼工作中使用,是
允許唯讀或是讀寫,如下:

image

 

以本例而言,我們設定 fileLine變數是可讀寫。

在「取得列數」的 script程式中,我們的程式寫法如下:

public void Main()
{
   StreamReader objReader = new StreamReader(@"c:\northwind.txt");
   while(!objReader.EndOfStream)
   {
Dts.Variables["User::fileLine"].Value = (int)Dts.Variables["User::fileLine"].Value+1;
    objReader.ReadLine();
   }
string test = Dts.Variables["User::fileLine"].Value.ToString();
            Dts.TaskResult = (int)ScriptResults.Success;
        }

在指令碼工作中使用變數的方式是使用Dts.Variables["變數名稱"],以本例而言,我們寫迴圈取得文件的列數值,放在變數中即大功告成!

接下來在 For迴圈中,做如此的設定:

image

每迴圈一次,就是讀資料流中的文件中的第 N 行,並且將這行資料列取出來。

所以在資料流中的程式碼元件,才會有
        while (!objReader.EndOfStream && nowCount <= this.Variables.fileLine)

這樣的程式撰寫。

不過也可以稍微再強調,程式碼元件要使用變數,要用 this.Variables.變數,這點和程式碼指令不同。

至於程式碼元件與程式碼指令的比較,請參考微軟官方網站

http://msdn.microsoft.com/en-us/library/ms136031.aspx

另外,程式碼元件中,取得指令的文件列後,利用下面指令將值塞到輸出欄位中:

EmployeeBuffer.AddRow();
EmployeeBuffer.EmployeeID = sLine.Substring(0, 10);

其中 EmployeeBuffer這個名詞,是因為我們的輸出資料表名稱為 Employee,而自然可以發現 EmployeeBuffer這個字眼在 BufferWrapper.cs中存在,如下圖:

image

也就是利用 EmployeeBuffer的 AddRow函數,増加一列,然後將值填入對應的欄位,其中
sLine.Substring(0,10),就是利用字串函數從第0個字元中取出10個字元資料。

這樣就完成一列的取出,看文件有幾列,利用 For迴圈把所有資料取出即大功告成!

最後控制流的處理結果:

image

而資料流的處理結果:

image

你沒看錯,資料流一次只處理一列資料,第一次處理文件的第一列,第二次處理文件的第二列,看你有幾列文字列(由於已經透過控制流取出資料列數,利用 For迴圈跑了)

最後資料的結果:

image

非常完美!

以上就是自行撰寫程式碼來處理資料,可以這麼說,有這個工具,無論你是什麼資料來源或目的,都可以利用自行撰寫程式的方式來完成你的需求!

image

Tags:

新增評論




  Country flag
biuquote
  • 評論
  • 線上預覽
Loading






NET Magazine國際中文電子雜誌

NET Magazine國際中文電子版雜誌,由恆逸資訊創立於2000,自發刊日起迄今已發行超過500篇.NET相關技術文章,擁有超過40000名註冊讀者群。NET Magazine國際中文電子版雜誌希望藉於電子雜誌與NET Developer達到共同學習與技術新知分享,歡迎每一位對.NET 技術有興趣的朋友們多多支持本雜誌,讓作者群們可以有持續性的動力繼續爬文。<請加入免費訂閱>

月分類Month List