[SSIS] 利用SSIS建置動態連接管理員來監控所有資料庫是否正常

說明：
本篇會使用到SSIS以下功能
1. Foreach迴圈容器
2. 動態連接管理員
3. SSIS錯誤處理 - ForceExecutionResult
4. Expressions：此篇並無詳述，只將語法直接列出。
5. 當會用Foreach迴圈容器去取值時，除了本篇監控資料庫STATUS之外，還有許多功能可用例如效能...
6. 完成以下程序，可將dtsx排入排程，定時監控資料庫，若有問題可以發出警告信或是簡訊

建立程序：
01. 建立DatabaseInfo資料庫，語法如下：
CREATE DATABASE [DatabaseInfo] ON  PRIMARY
( NAME = N'DatabaseInfo', FILENAME = N'D:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\DatabaseInfo.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB )
 LOG ON
( NAME = N'DatabaseInfo_log', FILENAME = N'D:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\DatabaseInfo_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO

02. 建立Instance_Info資料表並產生測試資料，語法如下：
USE [DatabaseInfo]
GO
CREATE TABLE [dbo].[Instance_Info](
        [I_Name] [varchar](30) NOT NULL,       --Instance Name
        [I_Address] [varchar](15) NOT NULL,   --IP Address
        [Version_N] [varchar](12) NOT NULL,  --資料庫版本
        [Version_D] [varchar](50) NOT NULL,  --資料庫版本(版號)
        [isMonitor] [varchar](2) NOT NULL,      --是否要監控(Y,N)
) ON [PRIMARY]
GO

USE [DatabaseInfo]
GO
INSERT [dbo].[Instance_Info] ([I_Name], [I_Address], [Version_N], [Version_D], [isMonitor]) VALUES (N'sqlcluster03', N'192.168.0.221', N'10.50.2500.0', N'SQL Server 2008 R2', N'Y')

INSERT [dbo].[Instance_Info] ([I_Name], [I_Address], [Version_N], [Version_D], [isMonitor]) VALUES (N'sqlcluster03-2', N'192.168.0.222', N'10.50.2500.0', N'SQL Server 2008 R2', N'Y')

03. [開始]>[所有程式]]>[Microsoft SQL Server 2008 R2]>在[SQL Server Business Intelligence Development Studio]上右鍵[以系統管理員身分執行]

04. 在[起始頁 - Microsoft Visual Studio]視窗中點依序展開[檔案]>[新增]>[專案]

05. 在[新增專案]視窗的[範本]區塊點選[Integation Services專案]，在下方區塊中[名稱]輸入[Dynamic_Connection]，[位置]輸入[D:\SSIS_SOURCE]後按下[確定]

06. 在[Dynamic_Connection - Microsoft Visual Studio]視窗中右邊[方案總管]>[Dynamic_Connection]>[SSIS封裝]>在[Package.dtsx]上右鍵[重新命名]輸入[Dynamic_Connection.dtsx]後按下[ENTER]

07. 在[Microsoft Visual Studio]視窗按下[是]

08. 在[Dynamic_Connection - Microsoft Visual Studio]視窗中的[Dynamic_Connection.dtsx]視窗>[控制流程]視窗右鍵[變數]，新增兩個變數如下
 第一組
        名稱：connString
資料類型：String
            值：不用輸入

 第二組
        名稱：instanceList
資料類型：Object
            值：預設值

09. 在畫面下方的[連接管理員]視窗中右鍵[新增OLE DB連接]

10. 在[設定OLE DB連接管理員]視窗中按[新增]

11. 在[連接管理員]視窗中的[伺服器名稱]輸入[192.168.0.221]，在[連接至資料庫]>[選取或輸入資料庫名稱]下拉選單找到[DatabaseInfo]後按下[確定]

12. 回到[設定OLE DB連接管理員]視窗中按[確定]

13. 在[Dynamic_Connection - Microsoft Visual Studio]視窗中左邊[工具箱]拉一個[執行SQL工作]到[Dynamic_Connection.dtsx]視窗的[控制流程]視窗中

14. 將[執行SQL工作]改名為[取得DB IP]

15. 雙擊[取得DB IP]

16. 在[執行SQL工作編輯器]視窗左邊點選[一般]，在右方[SQL 陳述式]下拉[Connect]找到[192.168.0.221.DatabaseInfo]

17. 在[SQL 陳述式]中的[SQLStatement]點選[...]

18. 在[輸入SQL查詢]視窗中輸入如下後按下[確定]
SELECT [I_Address]  FROM [DatabaseInfo].[dbo].[Instance_Info] WHERE [isMonitor]='Y'

19. 在[結果集]的[ResultSet]欄位下拉點選[完整結果集]

20. 點選左側[結果集]後按下[加入]

21. 在[結果名稱]欄位輸入[0]，在[變數名稱]下拉點選[User::instanceList]後按下[確定]

22. 在[Dynamic_Connection - Microsoft Visual Studio]視窗中左邊[工具箱]拉一個[Foreach迴圈容器]到[Dynamic_Connection.dtsx]視窗的[控制流程]視窗中，再拉一條流程線從[取得DB IP]到[Foreach迴圈容器]

23. 雙擊[Foreach迴圈容器]

24. 在[Foreach迴圈編輯器]視窗中點選[集合]，在[Foreach迴圈編輯器]>[Enumerator]下拉選[Foreach ADO 列舉值]，[ADO物件來源變數]下拉選[User::instance_List]

25. 在[Foreach迴圈編輯器]視窗中點選[變數對應]，在[選起變數以對應至集合值]欄位中的[變數]下拉選[User::connString]，[索引]輸入[0]後按下[確定]

26. 在[Dynamic_Connection - Microsoft Visual Studio]視窗中左邊[工具箱]將[執行SQL工作]及[傳送郵件工作]拉到[Dynamic_Connection.dtsx]視窗的[控制流程]>[Foreach迴圈容器]中

27. 將[Foreach迴圈容器]中的[執行SQL工作]更名為[Check SQL Server Status]，將[傳送郵件工作]更名為[發送警告信]

28. 重複步驟9到步驟12，產生一個新的連接管理員名稱為[192.168.0.221.DatabaseInfo 1]，並將[192.168.0.221.DatabaseInfo 1]更名為[Dynamic_Connection]

29. 在畫面下方的[連接管理員]視窗中在[Dynamic_Connection]右鍵[屬性]

30. 在[Dynamic_Connection - Microsoft Visual Studio]視窗中右邊的[屬性]視窗找到[Expressions]點選[...]

31. 在[屬性運算式編輯器]視窗中的[屬性運算式]下拉[屬性]點選[ConnectionString]

32. 在[屬性運算式編輯器]視窗中的[屬性運算式]的[運算式]點選[...]

33. 在[運算式產生器]視窗中下方[運算式]視窗輸入如下值，輸入完後可以按一下最下方的[評估運算式]看看有沒有錯誤，若沒問題案下[確定]
"Data Source="+@[User::connString]+";Initial Catalog=master;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;"

34. 回到[屬性運算式編輯器]視窗按下[確定]

35. 回到[Dynamic_Connection - Microsoft Visual Studio]視窗>[控制流程]視窗，雙擊[Check SQL Server Status]

36. 在[執行SQL工作編輯器]視窗左邊點選[一般]，在右方[SQL 陳述式]下拉[Connect]找到[Dynamic_Connection]

37. 在[SQL 陳述式]中的[SQLStatement]點選[...]

38. 在[輸入SQL查詢]視窗中輸入如下後按下[確定]
SELECT @@SERVERNAME

39. 回到[執行SQL工作編輯器]視窗後按下[確定]

40. 回到[Dynamic_Connection - Microsoft Visual Studio]視窗，在[Foreach迴圈容器]中將[Check SQL Server Status]拉條流程線到[發送警告信]

41. 在[流程線]上右鍵選[失敗]

42. 雙擊[發送警告信]

43. 在[傳送郵件工作編輯器]視窗中點選[郵件]，在右邊視窗的[郵件]>[SmtpConnection]下拉選[新增連接]

44. 在[SMTP連接管理員編輯器]視窗中輸入郵件主機參數後按下[確定]，本例如下圖

45. 回到在[傳送郵件工作編輯器]視窗中左邊視窗[郵件]>右邊視窗[郵件]的[From]輸入寄件者EMAIL[sqlalert@dba.local]，[To]輸入收件者EMAIL[justin@dba.local]

46. 回到在[傳送郵件工作編輯器]視窗中左邊視窗[運算式]>點開右邊視窗的[Expressions]>[...]

47. 在[屬性運算式編輯器]視窗的[屬性運算式]>[屬性]下拉到[Subject]後點選[...]

48. 在[運算式產生器]視窗中下方[運算式]視窗輸入如下值，輸入完後可以按一下最下方的[評估運算式]看看有沒有錯誤，若沒問題案下[確定]
"錯誤=>SQL Server："+ @[User::connString]+"發生連線異常請立即檢查"

49. 回到[屬性運算式編輯器]視窗按下[確定]

50. 回到[傳送郵件工作編輯器]視窗後按[確定]

51. 回到[Dynamic_Connection - Microsoft Visual Studio]視窗>[控制流程]視窗在[Foreach迴圈編輯器]上右鍵[屬性]

52. 在[Dynamic_Connection - Microsoft Visual Studio]視窗右邊的[屬性]>[Foreach迴圈容器 For Each迴圈]>找到[ForceExecutionResult]屬性後下拉選[Success]

53. 回到[Dynamic_Connection - Microsoft Visual Studio]視窗完成設定

P.S. [發送警告信]出現警告圖示是因為郵件主旨沒設定，但已在[Expressions]有設定可忽略。
54. 在[Dynamic_Connection - Microsoft Visual Studio]視窗點選偵錯圖示 ，下圖為所有DB皆OK的情形

55. 在DB中增加一筆不存在的資料庫資訊
USE [DatabaseInfo]
GO
INSERT [dbo].[Instance_Info] ([I_Name], [I_Address], [Version_N], [Version_D], [isMonitor]) VALUES (N'noDB', N'192.168.0.1', N'10.50.2500.0', N'SQL Server 2008 R2', N'Y')
GO
54. 在[Dynamic_Connection - Microsoft Visual Studio]視窗點選偵錯圖示 ，下圖為有DB無法連線的情況便會寄送警告信給管理員

信件內容如下圖