SSIS中的腳本—腳本組件

腳本組件提供另外一種在SSIS中使用腳本的方法，它只能在Data Flow中使用，不能在Control Flow中使用，它用來提供數(shù)據，接收數(shù)據，轉換數(shù)據。下面是三種類型：

    數(shù)據源類型：用來提供數(shù)據源，可以定義輸出類型，使用腳本填充數(shù)據。了個典型的例子是使用腳本來讀取復雜的文件，XML，或者更加過時的COBOL等等不能產生平面文件的文件
    數(shù)據目的類型：用來將數(shù)據填充到Excel，或者平面文件，或者將文件批處理到大型機上
    轉換型：用來接收數(shù)據，產生新的轉換數(shù)據，當SSIS提供的組件不能滿足需求的時候使用

使用腳本組件

這里我們舉例說明如何創(chuàng)建和使用腳本組建，我們處理一個文件，按照需求清洗文件中的數(shù)據。符合要求的數(shù)據將會被送到合適的表中，反之被送到另外一個表中。
我們處理一個包含聯(lián)系人信息的文件，數(shù)據庫對文件數(shù)據有一些驗證要求，不符合驗證標準的數(shù)據將會被送到另外一個表中人工處理。
使用下面的代碼來建立這兩個表：

CREATE TABLE dbo.Contacts
(
  ContactID int NOT NULL IDENTITY (1, 1),
  FirstName varchar(50) NOT NULL,
  LastName varchar(50) NOT NULL,
  City varchar(25) NOT NULL,
  State varchar(15) NOT NULL,
  Zip char(10) NULL
)  ON [PRIMARY]
CREATE TABLE dbo.ContactsErrorQueue
(
  ContactErrorID int NOT NULL IDENTITY (1, 1),
  FirstName varchar(50) NULL,
  LastName varchar(50) NULL,
  City varchar(50) NULL,
  State varchar(50) NULL,
  Zip varchar(50) NULL
)

兩個表的區(qū)別是ContactsErrorQueue表中的數(shù)據類型都是varchar(50)，并且可為空。下載Contacts.dat文件，這個文件中的數(shù)據邊界分割位置如下：

Field 　　　　　　Starting Position
FirstName          1
LastName          10
City                   25
State                 43
Zip                    51

數(shù)據的個數(shù)類似下面

Jason     Gerard         Jacksonville      FL      32276-1911

Joseph    McClung        JACKSONVILLE      FLORIDA 322763939

Andrei    Ranga          Jax               fl      32276

Chad      Crisostomo     Orlando           FL      32746

Andrew    Ranger         Jax               fl

新建一個package命名為ScriptComponent，拖放一個Flat File source，雙擊打開編輯界面，點擊新建連接，打開文件連接管理界面，將文件連接命名為Contacts Mainframe Extract。點擊瀏覽選擇Contacts.dat文件，選擇文件格式為Fixed Width，設置行寬為62，按照上面標設置列邊界。點擊Advance，按照上表為數(shù)據列命名。最后預覽數(shù)據如下圖1：

圖1

新建一個package命名為ScriptComponent，拖放一個Flat File source，雙擊打開編輯界面，點擊新建連接，打開文件連接管理界面，將文件連接命名為Contacts Mainframe Extract。點擊瀏覽選擇Contacts.dat文件，選擇文件格式為Fixed Width，設置行寬為62，按照上面標設置列邊界。點擊Advance，按照上表為數(shù)據列命名。最后預覽數(shù)據如下圖2：

圖2

將Flat File Source和Script Component連接起來，雙擊script component打開編輯界面，點擊Input Column標簽，可以看到在Input Name下拉列表中默認選擇Input 0，如果有其他輸入的話，這里可以選擇其他的輸入，如圖3。

圖3

選擇所有的輸入列。點擊Input and Output標簽，查看輸入列和輸出列的屬性。展開Output 0，選中Output Columns，點擊Add Column按鈕添加新列，命名為GoodFlag，修改數(shù)據類型為Boolean[DT_BOOL]。
點擊Edit Script按鈕打開編解界面，這里的代碼和Script task有所不同

' Microsoft SQL Server Integration Services Script Component

' Write scripts using Microsoft Visual Basic 2008.

' ScriptMain is the entry point class of the script.

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain

    Inherits UserComponent

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

        '

        ' Add your code here

        ''

        Return

    End Sub

End Class

引入的命名空間有所不同，添加了兩個新的命名空間Pipeline和Wrapper。ScriptMain類繼承UserComponent。沒有Mian()方法了，而是新的方法Input0_ProcessInputRow，它的參數(shù)Input0Buffer是一個自動產生的類，它包含所有的輸入輸出列，都有強類型的屬性。
這里一共產生了3個源文件：BufferWrapper，ComponentWrapper，ScriptMain。BufferWrapper包含自動產生的代碼緩沖，在這個例子中只有一個類Input0Buffer，它是自動產生的每次打開都回自動覆蓋，所以不要修改這里的代碼。文件ComponentWrapper中包含類UserComponent，主要的功能包含在類ScriptMain中。

要進行的驗證有：
? 除了字段zip，所有的字段都要求非空
? Zip的格式要求DDDDD-DDDD或 DDDD，這里D是一個0至9的數(shù)字，如果前5個數(shù)字驗證成功，后面沒有成功則截取前5個，后面的舍棄
? State必須是兩位大寫字符

這里使用正則表達式來驗證，如下：
? ^\d{5}([\-]\d{4})?$：驗證5位或者9位的郵政編碼
? \b([A-Z]{2})\b ：驗證2位的state

添加如下的代碼段：

' Microsoft SQL Server Integration Services Script Component

' Write scripts using Microsoft Visual Basic 2008.

' ScriptMain is the entry point class of the script.

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Imports System.Text.RegularExpressions

<Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute()> _

<CLSCompliant(False)> _

Public Class ScriptMain

    Inherits UserComponent

    Private zipRegex As Regex = New Regex("@^\d{5}([\-]\d{4})?$", RegexOptions.None)

    Private stateRegex As Regex = New Regex("@\b([A-Z]{2})\b", RegexOptions.None)

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

        'all fields except zip must have a value

        Dim isGood As Boolean = True

        If Row.FirstName_IsNull Or Row.LastName_IsNull Or Row.City_

        Then

            Row.GoodFlag = False

            Return

        End If

        If Not Row.Zip_IsNull Then

            Dim zip As String = Row.Zip.Trim()

            'zip must match regex if present

            If zipRegex.IsMatch(zip) Then

                Row.CleanedZip = zip

                isGood = True

            Else

                'try to clean up the zip

                If zip.Length > 5 Then

                    zip = zip.Substring(0, 5)

                    If zipRegex.IsMatch(zip) Then

                        Row.CleanedZip = zip

                        isGood = True

                    Else

                        isGood = False

                    End If

                End If

            End If

        End If

        If isGood Then

            Dim state As String

            state = Row.State.Trim().ToUpper()

            If stateRegex.IsMatch(state) Then

                Row.CleanedState = state

            Else

                isGood = False

            End If

        End If

        Row.GoodFlag = isGood

    End Sub

End Class

這段代碼執(zhí)行驗證規(guī)則，在方法Input0_ProcessInputRow內，首先檢查字段是否為空，除了zip，這里使用了ColumnsName_IsNull屬性，如果數(shù)據為空，它返回true。
下一步，如果Zip列不為空，并且通過正則表達式驗證，將它的值賦給CleanedZip,以便保存到目標表中。如果沒有通過正則表達式驗證，代碼檢查Zip長度是否大于5，如果是，截取前5位并使用正則表達式驗證，如果通過驗證，賦值給CleanedZip，isGood賦值為true，否則的話isGood賦值為false。
去掉State的前空格和后空格，轉換成大寫，然后使用正則表達式來驗證，如果通過驗證將它的值賦值給CleanedState，否則isGood賦值為false。
 根據isGood的值將數(shù)據送到相應的表中，拖放一個Conditional Split task，將script task和它連接起來。雙擊Conditional Split的編輯界面，使用條件GoodFlag == TRUE 添加一個輸出。這樣數(shù)據將會被分成兩部分。
添加兩個OLE DB Destination，一個指向Contacts表，一個指向ContactErrorQueue表。將Conditional Split和Contacts表連接起來。因為Conditional Split有多個輸出，選擇符合條件GoodFlag == TRUE的這一股，剩下的指向ContactsErrorQueue表。最后執(zhí)行這個package如圖4

圖4

最后有14行數(shù)據進入到Contracts表中，4行數(shù)據進入到ContactsErrorQueue表中，如圖5。

圖5

調試腳本組件

在腳本任務這個章節(jié)我們講述了如何使用斷點 調試，在腳本組件中我們不能使用這個特性，在代碼中設置的斷點會被忽略，你不許訴諸 于Row Count或者Data Riewer。
Row Count的功能很明顯，它顯示有多少數(shù)據通過數(shù)據流，Data Viewer更加方便。點擊Script Component和Conditional Split之間的連線 ，右擊選擇Data Viewers，在彈出的Data Flow Path Editor對話框中選擇Data Viewer標簽點擊Add，選擇Data Viewer Type為Grid，選擇想要查看的列，最后點擊保存。如圖6

圖6

再次運行這個package，將會看到Data Viewer界面，這里顯示從Script Component中輸出的數(shù)據，如圖7。點擊Play按鈕程序繼續(xù)運行，或關閉界面，程序也會繼續(xù)運行下去。

圖7

這里我們只能看到數(shù)據不能看到代碼的執(zhí)行情況。

總結

這一章中我們看到SSIS中的一些可以編寫腳本的任務，使用表達式動態(tài)設置屬性，使用表達式語言來完成數(shù)據轉換的任務，如何使用腳本任務控制工作流，使用腳本組建清洗數(shù)據。通過一些練習我們可以更好地掌握如何使用腳本。