Alert Notification through SSIS Package

Question

Alert Notification through SSIS Package

FMTONLY

Mr or Mrs. 500

Points: 578
More actions
September 20, 2011 at 5:02 am

#244026

I am working as a DBA and got a task related to SSIS package. By using SSIS package I need to develop a alert notification system.
There are 10 tables which has the information like servers related table, alert related,alert exclusion table, Notify table applications. There are different packages which collects the information into these 10 tables.
Now my SSIS Package requirement is:
1)A script is there which has to be execute every 5 min (I have this scrip and it takes required information from all the 10 tables into a single table(Notify table*)).
*Notify table is the table which contains information of the alert name, server name,alert message, ip address, timestamp, alert occurrence etc
2)After collecting the information to the Notify table, That package has to ignore the alert which are from Alert exclusion table(one among the 10 tables and it is the table which contains the information of the alerts which can be ignored)
3)If ‘X’ is the alert, by the first time it has to be logged to the notification table with occurrence as ‘1’. If the same alert is raised again, now instead of logging it again in the notification table, just it has to increase occurrence as ‘2’.
4)Once alerts occurrence has been updated, then it has to send a notification through mail based on the occurrences.
For Ex: If ‘X’ is an alert, is should be notified after 5 occurrences through mail from Notify table.(if the alert is occurred for 4 times it should not send notification)
Please let me know your views on this. I am very new to SSIS.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 1

jeevan619 (9/20/2011)
I am working as a DBA and got a task related to SSIS package. By using SSIS package I need to develop a alert notification system.
There are 10 tables which has the information like servers related table, alert related,alert exclusion table, Notify table applications. There are different packages which collects the information into these 10 tables.
Now my SSIS Package requirement is:
1)A script is there which has to be execute every 5 min (I have this scrip and it takes required information from all the 10 tables into a single table(Notify table*)).
*Notify table is the table which contains information of the alert name, server name,alert message, ip address, timestamp, alert occurrence etc
2)After collecting the information to the Notify table, That package has to ignore the alert which are from Alert exclusion table(one among the 10 tables and it is the table which contains the information of the alerts which can be ignored)
3)If ‘X’ is the alert, by the first time it has to be logged to the notification table with occurrence as ‘1’. If the same alert is raised again, now instead of logging it again in the notification table, just it has to increase occurrence as ‘2’.
4)Once alerts occurrence has been updated, then it has to send a notification through mail based on the occurrences.
For Ex: If ‘X’ is an alert, is should be notified after 5 occurrences through mail from Notify table.(if the alert is occurred for 4 times it should not send notification)
Please let me know your views on this. I am very new to SSIS.

Steps 1 , 2 and 3 can be easily done in Execute SQL Task ( better to make it a procedure). there is already a task called Send Mail task in SSIS these two should do the job for you.

Jayanth Kurup[/url]

FMTONLY Mr or Mrs. 500 Points: 578 More actions · Answer 2

Thanks for the immediate reply.

Step 1 can be done by using Execute SQL Task.

Please let me know how EXecute SQL Task can be used to do the Step 2 and step 3.

Thanks & Regards

Jeevan

Raunak Jhawar SSCoach Points: 15701 More actions · Answer 3

jeevan619 (9/20/2011)
I am working as a DBA and got a task related to SSIS package. By using SSIS package I need to develop a alert notification system.
There are 10 tables which has the information like servers related table, alert related,alert exclusion table, Notify table applications. There are different packages which collects the information into these 10 tables.
Now my SSIS Package requirement is:
1)A script is there which has to be execute every 5 min (I have this scrip and it takes required information from all the 10 tables into a single table(Notify table*)).
*Notify table is the table which contains information of the alert name, server name,alert message, ip address, timestamp, alert occurrence etc

Suggested Approach: EST will do.

2)After collecting the information to the Notify table, That package has to ignore the alert which are from Alert exclusion table(one among the 10 tables and it is the table which contains the information of the alerts which can be ignored)

Suggested Approach: Create sub sets of the Notify table as per the requirement and perform a UNION of all sets

3)If ‘X’ is the alert, by the first time it has to be logged to the notification table with occurrence as ‘1’. If the same alert is raised again, now instead of logging it again in the notification table, just it has to increase occurrence as ‘2’.

Suggested Approach: This is a dramatic requirement, which can be handled using SQL or a custom code. The custom code will manage an array or list of all events and record their counts. Insert that array or list into the SQL table.

4)Once alerts occurrence has been updated, then it has to send a notification through mail based on the occurrences.

Suggested Approach: SMT or a custom code to send notification email. I would recommend a custom code for better code manageability.

Happy to help.

Raunak J