一開始的想法，是將資料先分析好，將更新的擺一塊、新增的擺一塊，然後再分別作更新或新增的動作；但是這樣的作法，讓資料庫轉了兩次，直覺浪費許多時間及效能。於是開始思考有沒有可能在SSIS裡逐筆判斷該資料是需要更新或新增？費了好一些時間，終於讓我給試出來了！
作法：
因我們SSIS會利用變數來帶入每一筆資料來做邏輯判斷，所以這邊的SQL指令都必需先做成預存程序(STORED PROCEDURE)
select_test，來源資料庫
decdata，目的資料庫
1.首先，我們先新增一個和來源資料庫一模一樣的空資料庫，用來存放準備更新或新增的資料。在這邊，我們將select_test當作是來源資料庫，select_test_2則是空資料庫，decdata則是目的資料庫。
2.接著，我們開始設計SSIS模型；首先，設定好連接管理員，分別建立來源資料庫連接(SOURCE_DB)及目的資料庫連結(DEST_DB)。方法如下：
2-1.在連接管理員欄位的空白處按下滑鼠右鍵→新增OLE DB連接。
2-2.點選「新增」鈕。
2-3.選取資料庫路徑來源，設定帳號、密碼並選取資料庫名稱，按下「確定」鈕。
2-4.最後按下「確定」鈕，完成設定。
3. 在流程控制中，拉取工具箱元件，完成下列設計。
4.在流程控制空白處，按下澤鼠右鍵→變數，新增以下4個變數：
CHECK_TRANS_COUNT：用來記錄資料筆數。
SOU_age、SOU_first_name、SOU_PLAN_CODE：記錄來源資料庫的資料欄位。
5.在流程控制，「執行SQL工作中」按下滑鼠右鍵→編輯
5-1.在執行SQL工作編輯器左手邊，選取「一般」，編輯SQL陳述式。
將Connection指向我們的來源資料庫連結(SOURCE_DB)，
SQLstatement則是執行我們預先寫好的預存程序：CHECK_TRANS_DATA，它將會幫我們將來源資料庫的資料存進select_test_2資料庫中，並且幫我們計算有多少筆資料。
CREATE PROCEDURE [dbo].[CHECK_TRANS_DATA]
AS
TRUNCATE TABLE select_test_2
INSERT INTO select_test_2(PLAN_CODE,first_name,age)
SELECT S.PLAN_CODE, S.first_name, S.age
FROM select_test as S
SELECT COUNT(*) as RESULT FROM select_test_2
5-2.在「結果集」，我們設定將執行預存程序所得到的資料筆數結果存進「CHECK_TRANS_COUNT」變數中，按下「確定」。
6.接下來，在for迴圈容器中，我們建立了三個「執行SQL工作」元件，依序命名為：
「選取第一筆要新增或更新的資料列」
「判斷並執行新增或更新資料」
「刪除選取的資料列」
在for迴圈容器中，我們開始針對單一筆資料去做邏輯分析；由於資料庫如果沒有給明確的條件的話，沒辦法明白我們所謂的第N+1筆到底是哪筆資料，所以我設計的方式，是在執行for迴圈時，都去找第一筆資料，然後在邏輯分析執行完該筆資料後，刪除這筆資料，再執行下一次迴圈。這就是為什麼我們一開始我們要建立一個新的暫存來源資料庫select_test_2。
7.首先，先來設定for迴圈的邏輯：
7-1.在for迴圈容器上，按下滑鼠右鍵→編輯
7-2.在for迴圈邏輯器中，選擇左手邊「For迴圈」，開始編輯右手邊的「For迴圈屬性」
一般在程式中我們編輯for迴圈的指令是這樣：
for ( int i=0; i>0; i--)
而For迴圈屬性中：
InitExpression：建立一個for迴圈所需變數，即為程式中 int i；
在這邊，我們指定負責存放筆數數量的CHECK_TRANS_COUNT
EvalExpression：for迴圈條件判斷，即為程式中的 i>0；
在這邊，我們的條件為CHECK_TRANS_COUNT>0
AssignExpression：for迴圈的變數運算，即為程式中 i--；
在這邊，我們的變數運算為：
CHECK_TRANS_COUNT=CHECK_TRANS_COUNT-1
完成設定後，按下「確定」鈕。
8.選取「選取第一筆要新增或更新的資料列」，按滑鼠右鍵→編輯
8-1. 在執行SQL工作編輯器左手邊，選取「一般」，編輯SQL陳述式。
將Connection指向我們的來源資料庫連結(SOURCE_DB)，
SQLstatement則是輸入以下指令：
SELECT TOP 1 PLAN_CODE as resultA,first_name as resultB,age as resultC
FROM select_test_2
8-2.在執行SQL工作編輯器左手邊，選取「結果集」， 按「加入」，將我們查詢到的資料，
個別放入先前設好的三個變數（SOU_age、SOU_first_name、SOU_PLAN_CODE）中；
設定完成，按下「確定」鈕 。
9.選取「判斷並執行新增或更新資料」」，按滑鼠右鍵→編輯
9-1.在執行SQL工作編輯器左手邊，選取「一般」，編輯SQL陳述式。
將Connection指向我們的目的資料庫連結(DEST_DB)，
SQLstatement則是輸入：EXEC CHECK_UPorINSERT_DATA ?,?,?
請注意！！這邊的變數用 ? 來取代，我們在下一步驟會繼續說明。
這支預存程序 CHECK_UPorINSERT_DATA ，它會幫我們判斷該筆資料是該「新增」
至目的資料庫，或該「更新」目的資料庫裡的資料。
預存程序：CHECK_UPorINSERT_DATA的SQL指令碼如下：
CREATE PROCEDURE [dbo].[CHECK_UPorINSERT_DATA]
@PLAN_CODE varchar(5)
,@first_name varchar(10)
,@age int
AS
DECLARE @ROWCNTS INT
SELECT @ROWCNTS = COUNT(*)
FROM decdata
WHERE PLAN_CODE=@PLAN_CODE
IF @ROWCNTS > 0
Begin
UPDATE decdata SET first_name=@first_name,age=@age
WHERE PLAN_CODE=@PLAN_CODE
End
ELSE
Begin
INSERT INTO decdata (PLAN_CODE,first_name,age)
VALUES(@PLAN_CODE,@first_name,@age)
End
9-2.在執行SQL工作編輯器左手邊，選取「參數對應」， 按「加入」，把我們步驟在8-2，
儲存在SSIS變數（SOU_age、SOU_first_name、SOU_PLAN_CODE）依序放進來。
參數名稱這個屬性，就是指要將值放進第幾個 ? 裡（由0開始起算！設定時要注意！）
設定完成，請按下「確定」鈕。
10.選取「刪除選取的資料列」」，按滑鼠右鍵→編輯
10-1.在執行SQL工作編輯器左手邊，選取「一般」，編輯SQL陳述式。
將Connection指向我們的來源資料庫連結(SOURCE_DB)，
SQLstatement則是輸入：EXEC DELETE_DATA ?,?,?
預存程序：DELETE_DATA，是用來將判斷完的資料刪除，以便SSIS的迴圈開始讀下一筆資料。
CREATE PROCEDURE [dbo].[DELETE_DATA]
@PLAN_CODE varchar(5)
,@first_name varchar(10)
,@age int
AS
DELETE FROM select_test_2
WHERE PLAN_CODE=@PLAN_CODE and first_name=@first_name and age=@age
10-2.在執行SQL工作編輯器左手邊，選取「參數對應」， 按「加入」，把我們步驟在8-2，
儲存在SSIS變數（SOU_age、SOU_first_name、SOU_PLAN_CODE）依序放進來。
參數名稱這個屬性，就是指要將值放進第幾個 ? 裡（由0開始起算！設定時要注意！）
設定完成，請按下「確定」鈕。
最後，執行SSIS，即可將資料逐筆分析該更新或是新增的結果。
沒有留言:
張貼留言