25 October 2019

How to Set an alarm in SSIS

 

Introduction

 

This reusable and transversal module allows to manage and execute “Alerts”.

But…. What is an Alert?

An “Alert” is related to the monitoring of a specific situation and, if it occurs, it will entail the trigger an alert in various forms, being the most common an email.

This solution design was concerned with being a module:

1) Transversal

2) Reusable

3) Flexible in terms of configuration for any type of application

 

…A possible alert, requested by the business team, allows to validate, after the information loading process, whether a given list of agents is mapped in another database: if not, it will send an alert in the form of email to a recipient….

 

Functional Architecture

 

 

 

 

 

 

The architecture that was designed and assembled can be seen in the following image (implemented via the Microsoft SQL Server Integration Services tool):

 

 

[1] Components

The components which define the service of this module correspond to SQL Server Integration Services, organised in the section [1a]

 

Must be installed in the Project Deployment Model (SSIS) Catalog Folder “R-eusable F-ramework C-component”: This folder groups all Reusable Components (RFC Framework) thus avoiding duplication of functionality by the several projects, bringing only benefits.

 

 

SSIS Catalog with RFC Folder

Example of more RFC Solutions

[1a] 

The components that define the core system of this Monitoring and Alerts service are:

1) RFC_AlertsListener

2) RFC_AlertsDispatcher

By order:

(i) Manages the alert trigger process: It can be triggered in two modes: either M-annually (also called “On Demand”), by direct invocation or A-automatically triggered by SQL Agent Job (configured with temporal scheduling).

(ii) Handles any configured alerts by invoking the properly configured PlugIn Package that has defined validation and triggering business rules.

 

In addition to these core packages, there is also defined plugin packages, where each one, can implement a set of specific business rules; at this time, until this current day (others might and shall be implemented!), the only configured PlugIn Package has the following name and functionally:

RFC_AlertsDispatcherPlugin_CheckDBandSendEmail – This business rules and workflow package implements:

(i) Run a TSQL script configured and registered in a given configuration table (this TSQL has the proper business rules, it will determine if a given alert is triggered nor not).

(ii) Use the returned value (string properly formatted in JSON) by running this TSQL script and, it different from empty, pass it to the RFC component that allows sending an email (RFC_SendEmail) => in other words, the triggering alert!

The other components marked in the image are reusable (RFC), namely:

(i) RFC_SaveCentralLog – Allows centralized logging (all applications can register in a single log, logging messages instead of each application having its own log…)

(ii) RFC_SendEmail – Allows to send email (gets a properly structured JSON formatted string that records the type of emails to send), 1 or more

(iii) Other RFC-Like components can be added (and they should because we must avoid the normal copy-paste of packages)

 

[1b]

These configured SQL Agent Jobs components, resorted to proper naming conventions (see installation/configuration session), are associated with automatic alert invocation (the triggering process), where the core RFC_AlertsListener package will be invoked.

See the example below:

 

[2] SMTP Server

 

 

 

 

 

The SMTP server allows to sending emails (or Relay). It will be set in configuration table.

 

Installation and Configuration

 

Database (Data Model)

 

 

 

 

Views (SQL):

[View_RFC_SearchAlertsInformation]

To be used by client applications to query the state of particularly alert.

[View_RFC_CentralLog]

To be used by client applications to query log messages.

Tables:

CREATE TABLE [dbo].[ConceptDictionary_RFC_AlertsSchedulingType]
Defines the possible scheduling types: M-Manual (direct invocation), A-Automatic (by SQL Agent Job).

CREATE TABLE [dbo].[ConceptDictionary_RFC_AlertsType]
Defines the possible alert types: R-Reusable, C-Custom (specific).

CREATE TABLE [dbo].[ConceptDictionary_RFC_AlertsExecutionState]
Defines the possible types for an alert.

 

 

CREATE TABLE [dbo].[RFC_Alerts]
Defines an alert per se.

 

By order:

[RFC_AlertsId] [int] NOT NULL => PRIMARY KEY
[AlertUserFriendlyCode] [varchar](50) NOT NULL, => BUSINESS KEY (more “user-friendly”)
[FlagActive] [bit] NOT NULL, => Indicates whether the alert is active or not
[Priority] [tinyint] NOT NULL, => Alert Priority (not used anymore)
[Category] [varchar](100) NOT NULL, => Alert Category (for logical grouping)
[Title] [varchar](100) NOT NULL, => Alert Title
[Description] [varchar](256) NOT NULL, => Alert full description
[SchedulingType] [char](1) NOT NULL, => Scheduling Type (Manual or Automatic)
[JSONSchedulingSettings] [varchar](512) NULL, => Ignore
[SSISExecutorName] [varchar](256) NOT NULL, =>  Package name “PlugIn” That will handle alert business rules.
[ExtraParamsForSSISExecutorName] [varchar](8000) NULL, => Freeform text field to pass to “PlugIn” package.
[BusinessDBConnectionNameForSSISExecutorName] [varchar](50) NULL, => Database connection for running TSQL (can be used for field)
«ExtraParamsForSSISExecutorName»)
[Type] [char](1) NOT NULL, => Alert Type: Custom or Reusable
[ChannelDistributionType] [varchar](50) NOT NULL, => Type of Distribution Channel (Email or others….)
[ExtraParamsChannelDistribution] [varchar](8000) NULL, => Freeform text field complementary to distribution channel
[NextExecutionDate] [datetime] NULL, => control variable: ignore
[BeginExecutionDate] [datetime] NULL, => control variable: running start date
[EndExecutionDate] [datetime] NULL, => control variable: ending execution date
[ExecutionStateCode] [tinyint] NULL, => Alert status code
[ExecutionStateDescription] [varchar](2048) NULL, =>Complementary description to current state to alert
[LogFilterDetailBelowLevel] [int] NOT NULL, => Log detail level to use for this alert
[CountSuccessNumberTrigger] [int] NULL, =>  control variable: “Trigger” count
[CountSuccessNumberAlertsVerified] [int] NULL, =>  control variable: triggers count

CREATE TABLE [dbo].[RFC_SaveCentralLog]

Application log table.

CREATE TABLE [dbo].[ConceptDictionary_RFC_AlertsChannelDistributionType]

Distribution channel types; for now, only EMAIL (for information only).

CREATE TABLE [dbo].[ConceptDictionary_RFC_EmailProfilePriority]

Sets an email priority: Urgent, normal, low…

CREATE TABLE [dbo].[ConceptDictionary_RFC_SaveCentralLogType]

Log Type: Informational, Warning or Error.

CREATE TABLE [dbo].[RFC_BusinessDBConnectionName]

Business database connections.

CREATE TABLE [dbo].[RFC_ComponentResultExecutionCache]

Save and share execution states between components.

CREATE TABLE [dbo].[RFC_EmailConnectionProfile]

SMTP Connection Profiles.

CREATE TABLE [dbo].[RFC_EmailProfile]

Email Profiles.

CREATE TABLE [knowhow].[RFC_ComponentIndex]

RFC Components Index implemented so far.

Stored Procedures:

CREATE PROCEDURE [dbo].[Usp_RFC_GetComponentResultExecution]

Get the execution results of a determent component (as long as that component records its execution state).

CREATE PROCEDURE [dbo].[Usp_RFC_SaveCentralLog]

Allow to register the application log.

CREATE PROCEDURE [dbo].[Usp_RFC_SaveComponentResultExecution]
SSIS Catalog

Saves the result of an execution.

 

File System (Temporary)

 

 

 

 

 

 

 

Temporary files can be created through a definition of a FileSystem Area where you can save this type of files.

Integration Services SSIS Catalog

It is possible to visualise below:

1) Folder in the SSIS catalogue where RFC Projects should be installed

2) The name of the configured environment

3) Database Connections are configured through these environment variables

 

Monitor & Health Alert System

 

 

An application example

 

Business Requirements:

(…) After the process of loading the CallCenter Agents/Operators data source file in database ODS_XXXX, we must validate the agents that aren’t mapped : if we have at least one not mapped so we must sent an email with the email format below, to the following address email adminxxxx@domain.pt with CC to name@domain.pt.

Email Format:

SubjectALERTS – Agents list o with agents not mapped

Body: (html)

[|[param_cumprimento]|],
<br><br>
Below you can see the Agents List with agentes not mapped:<br><br>
<ul>
<li>Agent Code</li>
<li>Description</li>
<li>Associated Mapping Month</li>
</ul>

<p>
<u>Note: do not reply to this email.</u>

Where the keyword [|[param_cumprimento]|] must be substituted at runtime execution by “Good evening or Good morning or Good Afternoon” depending on the execution time.

1 – Configure the Alert

 

Let’s use the following RFC package (reuse, reuse, reuse):

(…)

RFC_AlertsDispatcherPlugin_CheckDBandSendEmail – this package implements:

(i) Executing a TSQL script configured (this TSQL has the business rules defined so that an alert can be fired or not);

(ii) Using the returned value (string formatted in JSON) by the execution of the TSQL script and if different from empty then pass it as an input parameter to the RFC that sends email (another RFC package: RFC_SendEmail; reuse, reuse, reuse!) => so, the alert is fired!

So, let’s build an example for this TSQL script, using these business rules:

DECLARE @val Varchar(4000);
DECLARE @cumprimento Varchar(50);
DECLARE @Total int
DECLARE @SQLString NVARCHAR(4000)
DECLARE @LogStep varchar(50)

BEGIN TRY
IF EXISTS
(
              SELECT [CodigoAgente],[DescricaoActividade],[DataFicheiro] FROM [dbo].[View_RFC_AgentesNaoMapeadosMatriz]
)
— alarm on !
BEGIN
DECLARE
@OutputFile NVARCHAR(255),
@FilePath NVARCHAR(255),
@FormatFile NVARCHAR(255),
@bcpCommand NVARCHAR(4000)

DECLARE @FlagError bit
DECLARE @ErrorDesc nvarchar(1024)

DECLARE @BcpOutput table(line varchar(256))

— bcp params: export query data to file

SET @SQLString = N’SELECT [CodigoAgente],[DescricaoActividade],[DataFicheiro] FROM [dbo].[View_RFC_AgentesNaoMapeadosMatriz]’
SET @bcpCommand = ‘bcp “‘ + @SQLString + ‘” queryout ‘
SET @FilePath = ‘C:\temp\RFC_AlertSystem\’
SET @OutputFile = ‘RFC_Alerts_’ + cast(NEWID() as varchar(36)) + ‘.csv’
SET @FormatFile = ‘-c -t; -T’
SET @bcpCommand = @bcpCommand + ‘”‘ + @FilePath + @OutputFile + ‘” ‘ + @FormatFile + ‘ -S’+ @@servername

declare @retValue int

set @LogStep = ’02-exporting data to file’

insert into @BcpOutput — get bcp execution detail
exec @retValue = master..xp_cmdshell @bcpCommand

IF (@retValue <>0)
— bcp error !
select top 1 @FlagError = 1, @ErrorDesc = line from @BcpOutput where line like ‘Error = %’
ELSE
— all ok, yes!
select @FlagError = 0, @ErrorDesc = ”

IF (@FlagError <>0)
RAISERROR (@ErrorDesc, 16, 1) — raise error
ELSE
BEGIN
— set JSON fields to “SendEmail” component

set @LogStep = ’03-setting JSON return info’

— set RFC “param_cumprimento”
IF (DATEPART(HOUR, GETDATE()) between 18 and 0)
SET @cumprimento=’Good Evening’
ELSE
IF (DATEPART(HOUR, GETDATE()) > 12 and DATEPART(HOUR, GETDATE()) < 18)
SET @cumprimento=’Good Afternoon’
ELSE
SET @cumprimento=’Good Morning’

set @val =
‘[
{
“RTP”:[
{
“name”:”param_cumprimento”,
“value”:”‘ + @cumprimento + ‘”
},
{
“name”:”param_ficheiro_caminho”,
“value”:”‘ + replace(@FilePath + @OutputFile,’\’,’\\’) + ‘”
}
],
“PerfilEmail_id”:1
}
]

select @val — alarm!
END
END
ELSE
select ” — no alarm: empty string
END TRY
BEGIN CATCH
— control errors to create custome errors

DECLARE @fullmessage varchar(1024)

set @fullmessage = @LogStep + ‘ : ‘ + ERROR_MESSAGE()

RAISERROR (@fullmessage, 16, 1) — error
END CATCH

 

Important Notes:

  • Marked in red, it is possible to stand out when the alert should be generated and is returned a properly structured JSON with the email to be sent (the RFC Email Component will receive this JSON Input Parameter and create and send the necessary emails associated)

In this JSON, stands out the parameters marked in bold that will be used to dynamically define the email; Also, noteworthy is the email profile to be used given by «Perfilemail_id : 1», in this case, the profile with id=1 configured in the table as we can see below.

  • Marked in green it is possible to stand out the alarm non-existence and consequently it returns empty string.

 

2 – Configure the Email Profile

 

 

3 – Configure Trigger

 

 

a) AUTOMATIC:

Scheduling Type=’A’

It is necessary to create the SQL Agent Job that will act as a trigger and set a Temporal Scheduling (example: run daily, always at 14h).

See above in this article the – Creation of the respective Job – section.

 

b) ONDEMAND:

Scheduling Type=’M’

Here it will be invoked directly through SQL Server Integration Services, with proper parameter transition.

Example:

 

Via Stored Procedure:

DECLARE @return_value int,
@return_state_code int,
@return_state_desc varchar(1024)

EXEC @return_value = [dbo].[Usp_RFC_TriggerAlertManualy]
@param_alert_id = ?,
@param_LoggroupId = ?,
@return_state_code = @return_state_code OUTPUT,
@return_state_desc = @return_state_desc OUTPUT

SELECT @return_state_code as N’@return_state_code’,
@return_state_desc as N’@return_state_desc’

And that’s it!

Of course, there is a lot of detail that was filtered in this article but if you are interested in a better understanding of this subject please address me.

Thank you!

 

 
    Pedro Mesquita         
   Senior Consultant

 

Blog