Lab6：使用變數

簡單變數使用

這個範例，簡單練習如何建立變數，並將變數套用在 SSIS 的 Execute SQL Task 中。

1. 在變數窗格中加入變數

2. 將變數套用到 Execute SQL Task

在 Execute SQL Task 的編輯視窗中，設定以下屬性：

• SQLSourceType => Variable
• SourceVariable => truncateStgCustomer

在 SSIS Objects 中套用變數

在底下範例，我們將練習，如何建立 run time 才能決定值的變數，而不是在 design time 就決定的。

在 Expression 中套用變數

下面例子練習，如何將變數套用在運算式中。

1.建立變數

2.在[運算式產生器]中設定運算式

點一下變數窗格最右側的(...)按鈕，就會跳出運算式產生器（Expression Builder），它是一個相當方便的操作介面，你可以介面中找到以下功能：

• 左邊樹狀選單：系統變數或自訂變數
• 右邊樹狀選單：SSIS expression function
• 評估運算式：expression test

使用 Expressions 控制 Data Flow 行為

很多 SSIS 物件都有 Expression 屬性可以設定。各個物件的 expression 都有各自的功用。 底下練習將示範如何設定 Data Flow Task 執行時所使用的緩衝區大小。 Data Flow Task 的緩衝區大小由 DefaultBufferMaxRow 屬性控制，預設值為 10000 。 我們可以透過 Property Expressions 來變更這些屬性設定。

1. 建立變數，用來判斷執行日期

下面例子中，我們先建立一個變數，用以取得執行的星期。

(DT_UI1)DATEPART( "Weekday", GETDATE())

2. 設定 Data Flow Task 的 Expressions 屬性

我們要將變數套用到 Data Flow 上，所以開啟[屬性運算式編輯器]，

在[屬性運算式編輯器]，加入二個屬性運算式：

• DefaultBufferMaxRow => @[User::dayOfWeek] == 6 || @[User::dayOfWeek] == 7 ? 20000 : 10000
• DefaultBufferSize => @[User::dayOfWeek] == 6 || @[User::dayOfWeek] == 7 ? 20971520 : 10485760

以上設定， User::dayOfWeek 是我們自訂的變數，會回傳執行日期。 我們將它套用到這二個屬性的運算式中，以動態決定其屬性值。 這個設定，若 package 的執行時間若為週末，則使用較大的緩衝區；若為週間日，則使用較小的緩衝區，以減少資源的耗用。

關於這二個屬性，你可以在右邊連結找到詳細說明。MSDN：資料流程效能的功能

使用 Master Package

底下範例，我們將練習如何建立 Master Package ，並且在父封裝中 invoke 子封裝，並且傳遞參數值給子封裝。 例子中會用到在 lab5 中建立的二個封裝 Staging.dtsx 和 DimCustomer_New.dtsx 。

設定子封裝

DimCustomer_New.dtsx 是我們要由主封裝中啟動的子封裝中的其中一個。 底下練習將示範主封裝(Master)該如何送出參數值，以及子封裝(DimCustomer_New)該如何取得參數值。

1. 在 DimCustomer_New 中加入參數

開啟 DimCustomer_New 封裝，並在[參數]標籤中新增一個參數。

這個 MasterPackageID 是個 package-scoped 參數，用來接收父封裝的傳來的識別碼。 不過由於這個封裝不一定會由父封裝叫用，所以底下我們會再增設一個 invokePackageID 變數。 若是由父封裝啟動，則 invokePackageID 就等於 MasterPackageID，若不是由父封裝啟動，則 invokePackageID 就會等於子封裝本身的 PackageID （本身的 PackageID 可由系統變數最得）。

2. 在 DimCustomer_New 中加入變數

在 DimCustomer_New package 中加入以下三個變數

• newRecordCount：The number of rows added to the Customer dimension.
• oldRecordCount：The number of rows modified to the Customer dimension.
• invokePackageID：identifier from MasterPackageID parameter or package's own identifier

在 invokePackageID 的運算式中，我們去判斷參數 MasterPackageID 是否有值，若沒有則使用自已封裝的 PackageID .

@[$Package::MasterPackageID] == "" || ISNULL(@[$Package::MasterPackageID])  ? @[System::PackageID]  : @[$Package::MasterPackageID]

在父封裝中，我們希望能夠知道子封裝是否有新增資料，所以當 DimCustomer_New 封裝結束之前，我們必須將這三個變數值會儲存至 dbo.ETLHistory 資料表，讓父封裝可以判斷是否有資料列新增。

CREATE TABLE [dbo].[ETLHistory](
	[PackageID]				[uniqueidentifier]	NOT NULL,
	[RunTime]				[datetime]			NOT NULL,
	[NewRecordCount]		[int]				NOT NULL,
	[ModifiedRecordCount]	[int]				NOT NULL,
	CONSTRAINT [PK_ETLHistory] PRIMARY KEY CLUSTERED 
	(
		[PackageID] ASC,
		[RunTime] ASC
	)
)

3. 取得修改筆數

接下來我們將修改 DimCustomer_New 封裝的流程，以取得新增筆數與筆數。

在 Control Flow 中加入一個 Execute SQL Task 用來取得修改筆數，並儲存在 oldRecordCount 變數中。

設定 Task 的屬性

SELECT	COUNT(*) as ModifiedRowCount
FROM dbo.DimCustomers AS C
INNER JOIN stg.DimCustomersUpdate AS U ON U.CustomerKey = C.CustomerKey

在[結果集]標籤中，將查詢結果儲存至 oldRecordCount 變數。

4. 取得新增筆數

在封裝的 Data Flow 頁籤中加入一個 Row Count Transformation 用來取得新增筆數，並儲存在 newRecordCount 變數中。

由 Row Count Transformation 的編輯視窗中，將資料列數指派給 newRecordCount 變數。

5. 記錄修改與新增筆數

在 Control Flow 中加入一個 Execute SQL Task 用來記錄異動的筆數。

INSERT dbo.ETLHistory
(
	PackageID,
	NewRecordCount,
	ModifiedRecordCount
)
SELECT ?, ?, ?

設定參數對應：@p1,@p2,@p3 會依序對應到 SQLStatement 中的每一個「?」。

建立父封裝

1. 加入 Execute Package Task

新增一個 package ,命名為 Master.dtsx ，並加入以下 Task 。

2. 設定參考封裝

依照以下步驟，設定每一個 Execute Package Task 要 invoke 的子封裝。

2.1 設定 Task1 的啟動封裝 (FillStageTables)

ReferenceType有二個選項：

• 專案參考：被invoke的封裝如果在專案內部，則使用專案參考。
• 外部參考：被invoke的封裝如果在 SSIS 伺服器或者檔案系統中，則使用外部參考。

2.2 設定 Task2 的啟動封裝 (DimCustomerNew)

2.3 設定 Task2 中，要傳遞給子封裝的參數值

在[參數繫結]頁籤中設定要傳遞給子封裝參數值，要將什麼資料參遞給子封裝，就是由這裡指定。

DimCustomer_New 是這個 Task 要 invoke 的封裝，在前面範例中，我們在該封裝中所設定的參數，都會在這裡的[子封裝參數]的下拉選單中。 若你想將哪個值傳遞給它，就將該變數或參數繫結給它。

底下設定會將主封裝的 PackageID 傳遞給子封裝的 MasterPackageID 參數。

3. 設定變數

在父封裝中加入一個變數 totalRecordCount ，用來記錄總筆數。

4. 設定 SQL Task 內容

這個 SQL Task 主要是用來取得 dbo.ETLHistory 中最新一筆記錄的 NewRecordCount+ModifiedRecordCount。

4.1 連線內容

SELECT TOP(1)
    H.NewRecordCount + H.ModifiedRecordCount AS TotalRecordCount
FROM 
    dbo.ETLHistory AS H
WHERE 
    H.PackageID = ?
ORDER BY H.RunTime DESC;

4.2 參數對應

因為在 SQL Statement 中用到了一個「?」，所以在「參數對應」標籤中，我們將這個 @p1 參數套用 System::PackageID 變數。

4.3 結果集

由於4.1步驟中，將這個 Task 的結果設定為單一資料列，所以可以使用一個純量變數來取得。

設定父封裝

1. 設定工作優先順序

2. 設定工作優先順序的條件

完成圖

當 DimCustomerNew 這個工作執行完畢後，我們在 GetTotalRecordCount 工作中取得異動總筆數，並存放在 @totalRecordCount 變數中。 同時在 Precedence Constraint 中加入判斷，若異動總筆數>0，則繼續執行 ProcessSSAS 封裝。此處的 ProcessSSAS 只是一個空的示範封裝。