One of the common problems faced when using Matillion hosted on an Azure virtual machine is the inability to send failure emails when a scheduled job does not run successfully. Without a solution outside of Matillion, users would need to start the virtual machine to check the status of a job. Depending on the number of jobs and their scheduled run times, this could become a time-consuming manual process of starting and stopping the virtual machine.

The solution to this problem combines the Azure Queue Message component within Matillion, Azure Queues within an Azure Storage Account, and an Azure function using an Azure Queue Trigger written using .Net Core 3.1. When a job fails, the Queue Message component will send a message to the Queue listening for failures. This triggers the Azure Function, which sends an email notification of a failure, including the job title, to any email address added in the function.

Setting Up Azure Storage Account

To get started, ensure that there is an Azure Storage Account created. There is likely one created for the resource group that the Matillion VM is using but a new one can be created as well if there is a desire to keep messages in a separate location.

 

Within the created storage account, there is an option for ‘Queues’ under ‘Queue Service’. Selecting the ‘+ Queue’ button at the top of the pane will bring up an option for entering the Queue name. This is the only setting that is needed. Keep in mind that Queues can be used for both success and failure emails if desired and this name will be entered in the Matillion component.

 

For Matillion to be able to send messages to the queue, access needs to be given to the storage account. There are multiple ways to provide access to the storage account with the easiest of these options being to provide the VM owner access to the account. To do this, navigate to the ‘access control (IAM)’ pane within the storage account. Clicking the ‘+ Add’ button will bring up an ‘Add Role’ pane allowing the user to add the owner access to the VM. For more options on granting permission, check the Matillion documentation here.

Setting Azure Credentials Within Matillion

 

Within Matillion, right clicking on the selected environment will allow the user to edit the environment setup. Select ‘Instance Credentials’ for the ‘Azure Credentials’. This will allow Matillion to access the storage account and send messages to the Queue.

Creating Azure Queue Storage Message Component

 

For this component, we are using an environment variable called ‘AuditJobName’ as the message to be sent. This variable is set using the ‘Set Scalar Variables’ setting of the orchestration job. This variable is populated with the automatic variable ‘job_name’. While any text can be entered as the message, using a variable for the job name allows for the same component to be used in multiple different jobs or within an audit framework without the need for multiple different messages. For more information on the automatic variables that can be used in a Matillion audit process, check out the Matillion documentation here.

Create Azure Function

 

To create the function app, search for ‘Function Apps’ within the Azure portal. This will show the above pane allowing the settings to be chosen for all apps within this Function App container. Choose the necessary subscription and resource group for the project. Choose .Net Core as the runtime stack which will default the version to 3.1 or above depending on the most recent version. This allows for the function to be written using C# code. Select ‘Review + Create’ to complete the setup.

 

After completing the Function App setup, add a function within the app. When creating new functions there are many templates to choose from including IotHubTrigger, EventHubTrigger, and TimerTrigger. For this project, choose the QueueTrigger then complete the settings for name, Queue name – which was created earlier, and choose a storage account.

 

After the function is created, navigate to the configuration settings within the Function App. Application settings need to be added for the function to read the proper values. The settings that need to be added are EmailSubject, SmtpHost, SmtpPassword, SmtpPort and SmtpUser. The Smtp settings may vary depending on email provider. This example is using an Office365 email. The SmtpHost is ‘smtp.office365.com’ and the SmtpPort is ‘587’. The SmtpUser and SmtpPassword is the email and password that will be used for sending the failure email.

using System;
using System.Text;
using System.Net;
using System.Net.Mail;
using System.Configuration;
using System.Threading.Tasks;
 
public static void Run(string myQueueItem, TraceWriter log){
    log.Info($"C# Queue trigger function processed: {myQueueItem}");
    var emailAddress= myQueueItem;
    string fromEmail = Environment.GetEnvironmentVariable("SmtpUser");

    string toEmail = myQueueItem;
    int smtpPort = Int32.Parse(Environment.GetEnvironmentVariable("SmtpPort"));
    bool smtpEnableSsl = true;
	
    string smtpHost = Environment.GetEnvironmentVariable("SmtpHost"); // your smtp host
    string smtpUser = Environment.GetEnvironmentVariable("SmtpUser"); // your smtp user
    string smtpPass = Environment.GetEnvironmentVariable("SmtpPassword"); // your smtp password
    string subject = Environment.GetEnvironmentVariable("EmailSubject");
    
    string message = ($"The matillion job: {myQueueItem} has failed. Please login to matillion and check the error. \nNavigate to Project -> Task History to see the failing job. \n\nThanks,\nData Admin \nAzure Queue Message");
    
    MailMessage mail = new MailMessage();
    SmtpClient client = new SmtpClient();
    
    client.Port = smtpPort;
    client.EnableSsl = smtpEnableSsl;
    
    client.DeliveryMethod = SmtpDeliveryMethod.Network;
    client.UseDefaultCredentials = false;
    
    client.Host = smtpHost;
    client.Credentials = new System.Net.NetworkCredential(smtpUser, smtpPass);
    
    mail.Subject = subject;
    mail.From = new MailAddress(fromEmail);
    
    mail.Body=message;
    mail.To.Add(new MailAddress("*****@******.com"));
    
    try{
        client.Send(mail);
        log.Info("Email sent");
    }
    catch(Exception ex){
        log.Info($"Catch: {ex}");
        log.Verbose(ex.ToString());
    }
}

Above is an example of the code that needs to be inserted within the Azure Function. This may be modified to get the necessary result. Ensure that the environment variables that we set earlier exactly match the variables used within the code. Multiple emails may be added using ‘mail.To.Add(new MailAddress());’ multiple times. The best way to test the function is using the Visual Studio Code extension that can be found here. The result of a successful run is the email shown below.