Lab16：在SSIS封裝中使用自訂程式碼

這個練習將會使用 Data Profiling Task 去分析資料庫中客戶電子郵件資料的樣版（Pattern）。並將分析結果輸出到 XML 檔案中。 然後再使用 Script Task 來開啟這個檔案，以讀取 XML 文件中樣版的資料。

根據得到的樣版，再使用正規表達式去驗證另一個資料庫中的電子郵件資料，找出不符合這個樣版的郵件。

使用 Data Profiling Task 分析資料

這個練習將會使用 Data Profiling Task 去分析資料庫中客戶電子郵件資料的樣版（Pattern）。並將分析結果輸出到 XML 檔案中。

加入變數

新增一個封裝，加入二個 package-level 變數。

加入連線

這個封裝將讀取 AdventureWorksDW2012.dbo.vTargetMail 檢視表中的資料，分析後再存到 XML 檔。 所以建立二個連線，一個是 ADO.NET Connection 連線到 AdventureWorksDW2012 資料庫，另一個是 File Connection ，連結到存放輸出的 EmailProfiling.xml 檔。

加入分析檔工作（Data Profiling Task）

加入 Data Profiling Task 以便分析資料庫中客戶電子郵件。

設定 Destination 和 OverwriteDestination 屬性

設定「分析檔要求」（Profile Request）

在「分析檔要求」頁面中加入「資料行模式分析檔要求」（Column Pattern Profile Request）

並設定資料來源的連線及要分析的資料表欄位。

執行 ProfileEmail Task

在執行後，你可以開啟 XML 檔，查看執行結果。

這個分析結果顯示，整個資料庫中的 email 總共可以歸納出二個正規表示式。

使用 Script Task 讀取資料

接下來我們將使用 Script Task 來開啟這個 XML 檔案，並讀取文件中樣版的資料。

加入 Script Task

加入 Script Task ，並連接上頭 ProfileEmail Task。

設定 Script Task

設定 Script Task 的進入點及要讀寫的變數。

編輯指令碼

1. 加入 System.Xml Namespace

2. 在 ScriptMain 類別中加入變數的定義。

3. 修改 Main Method 中的程式碼。

public void Main()
{
    // TODO: Add your code here

    string profilePath;
    XmlDocument profileOutput = new XmlDocument();
    XmlNamespaceManager profileNSM;
    XmlNode regExNode1;
    XmlNode regExNode2;

    // Open output file.
    profilePath = fileName;
    profileOutput.Load(profilePath);
    profileNSM = new XmlNamespaceManager(profileOutput.NameTable);
    profileNSM.AddNamespace("default", profileNamespaceUri);

    // Get regExNodes
    regExNode1 = profileOutput.SelectSingleNode(erx1Path, profileNSM);
    regExNode2 = profileOutput.SelectSingleNode(erx2Path, profileNSM);

    // Assign variable values
    Dts.Variables["User::EmailRegEx1"].Value = regExNode1.Value;
    Dts.Variables["User::EmailRegEx2"].Value = regExNode2.Value;

    // Show variable values
    //MessageBox.Show(Dts.Variables["User::EmailRegEx1"].Value.ToString());
    //MessageBox.Show(Dts.Variables["User::EmailRegEx2"].Value.ToString());

    Dts.TaskResult = (int)ScriptResults.Success;
}

執行 Script Task

將 VSTA 存檔後關閉，並執行這個 Script Task，它會使用 MessageBox 回應二個訊息，並顯示由 XML 文件中讀取到的資訊。

上面例子，我們在 Script Task 中，將資訊值儲存到變數中， 在運用上，你可以在 Script Task 的輸出中，使用運算式條件約束來判別結果，以便將不同的結果接不同的處理工作。

使用 Script Component 驗證資料

接下來，我們將利用上面例中得到的二個Email格式資訊，使用 Script Component 撰寫程式碼來驗證另一個郵件清單中的資料是否有符合這二個格式。

Prepare the Environment

建立儲存驗證結果的資料表

CREATE TABLE dbo.EmailValidated
(
    CustomerKey INT NOT NULL,
    EmailAddress NVARCHAR(50) NULL,
    EmailValid BIT NULL
);

停用前例中的顯示

在前例中，我們將讀取到的二個資料，由 MessageBox 顯示出來。我們必須註解掉這二行指令，以便程式可以繼續往下處理。

//MessageBox.Show(Dts.Variables["User::EmailRegEx1"].Value.ToString());
    //MessageBox.Show(Dts.Variables["User::EmailRegEx2"].Value.ToString());

Use the Script Component

In this exercise, you use the Script Component to validate email addresses against regular expressions.

加入 Control Flow

在 Control Flow 中加入 Data Flow Task ，以便執行資料處理。

加入資料來源

在 Data Flow Tab 中加入一個 ADO NET Source 。這個資料來源就是要被驗證的郵件清單。

加入指令碼元件

設定指令碼元件

1. 在 Script 頁面中，設定 ReadOnlyVariables 屬性，指定要讀取的變數。

2. 在 InputColumn 頁面中，選擇以下二個欄位。

3. 在 Inputs And Outputs 頁面中，展開 Output0 ，並加入一個輸出欄位 (EmailValied). 並將資料型別設定為 Boolean 。

4. 回到 Script 頁面，執行「編輯指令碼」

撰寫指令碼

1. 在開啟 VSTA 編輯器後，你可以看到專案中包含三個檔案。

2. 在 Main.cs 中加入 Using System.Text.RegularExpressions 命名空間。

3. 在 Input0_ProcessInputRow() 中加入以下指令碼。

public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        /* Add your code here */
        Row.EmailValid =
            Regex.IsMatch(Row.EmailAddress, Variables.EmailRegEx1, RegexOptions.IgnoreCase | RegexOptions.Compiled) |
            Regex.IsMatch(Row.EmailAddress, Variables.EmailRegEx2, RegexOptions.IgnoreCase | RegexOptions.Compiled);
    }

4. Save the script and close VSTA

加入資料目的地

在 Data Flow Tab 中加入一個 ADO NET Destination 。這個ADO.NET目的地是要用來存放已驗證的郵件清單。

執行封裝

檢驗結果

當封裝執行結束，你可以用以下 TSQL 找出驗證沒過的郵件清單。

SELECT *
FROM dbo.EmailValidated
WHERE EmailValid = 0;