Rajendra Gupta
Azure SQL Database

Azure Automation: Automate data loading from email attachments using Azure Logic Apps

May 11, 2021 by

This article will explore Azure automation for automatic data loading into Azure SQL Databases using the Azure Logic App.

Introduction

Suppose you have an Azure SQL Database for product catalog for your online shopping portal. You receive a CSV file in a specific mailbox and format for various products. You require to import data from these CSV files into Azure SQL Database.

In the following high-level diagram, we can visualize the overall process. In this process, you can use the following steps to achieve the requirements.

  • Manually extract the attachment from the email and save it to a local directory
  • Browse to the Azure portal, navigate to the storage account container and upload the file into the INPUT folder
  • Import data into Azure SQL Database tables

Azure SQL Database

If we do this task manually, it might be a cumbersome process to execute daily. Therefore, you can look for automation using various ways.

  • You can use ETL packages using the SQL Server Integration Services
  • Azure Data factory

However, in these cases, it is a slightly complicated process to extract attachment from the email (on email arrival) and save it to the storage container automatically.

In the article, Auto-scaling Azure SQL database with Azure Logic Apps, we explored azure logic apps usage for automatically scaling resources for your azure databases.

We will cover the solution in a series of two articles. We will learn how you can use Azure Logic Apps for simulating the problem we defined earlier.

In this first part, we will implement the following things:

  • Azure Logic apps to read an email, get its attachment
  • Save the attachment into the Azure Storage container blob
  • Send email confirmation once the attachment is uploaded to the Azure container

Requirements

To start working with this article, you require the following database environment.

  • Azure SQL Database: As we import data into a database table, you require an active Azure database instance. Here, we have the following [labazuresql] database in the azuredemoinstance.database.windows.net server

    Azure database dashboard

  • Azure Storage account and container: We require an Azure storage account and blob container for storing input and output files. Here, I have the following setup:

    • Storage account: azureauditdatastorage
    • Blob Containers: input and output

    Azure Storage account

  • Azure Logic App account: Azure Logic app is an excellent tool to connect and build integration solutions, workflow in a simplified manner. To use the functionality, we require a logic app account. Refer to the article Automatic Scale up and down Azure SQL database with Logic Apps for more details. Here, I have [azuredataimport] logic app account, as shown below

    Azure Logic app designer

Azure Automation for extracting Email attachments and store into Azure blob storage

In the initial part of the document, we configure logic apps to extract attachment on email arrival and save into a blob storage container’s input folder.

Extract Email attachment

Let’s assume that we received a CSV file for import in the outlook.com mailbox. To create a workload, we can use a suitable template, or we can start with a blank logic app template, as shown below.

Extract Email attachment

In the app designer, search for Outlook. It returns all matched components for it. Click on the Outlook.com component as a source component.

Outlook.com connector

It opens a list of items in the triggers. We want to generate an action when a new email arrives in the Inbox of outlook.com. Therefore, click on When a new email arrives from the Triggers menu.

when a new email arrives

You need to sign in to Outlook.com with your credentials.

Outlook credential

Enter your email address (user name) and password for authentication to your email account.

Enter your email address

Azure logic apps ask you to accept the following permissions for the Outlook.com mailbox:

  • Read and write access to your email
  • Send mail as you
  • Have full access to your contacts
  • Have full access to your calendars
  • Maintain access to data

Provide permissions

Click on Yes. It opens the configuration page for when a new email arrives. In the configuration, enter the following details:

  • Folder: Inbox
  • Importance: Any
  • Only with Attachments: Select Yes because we wanted to upload email attachments into the Azure blob container
  • Include attachments: Yes
  • From: Usually, we receive an email from a specific email address such as vendor email id. Therefore, enter the email id in the field
  • Subject filter: We can further filter emails from the Inbox using a specific subject filter. Here, we want to check emails with the subject line – Azure Data Import CSV

filter emails

Click on +New step. Now, we need to define what action you need to take once a new email arrives.

Choose an operation

Click on Outlook.com and navigate to actions, select Get attachment.

Get attachment.

In the Get attachment configuration, it requires configuration for Message Id and Attachment Id.

  • Message Id: Click on the Add dynamic content and select Message id as shown below

    Add dynamic content

  • Attachment Id: Here, we need to map the id of the attachment we want to download. Click on Add dynamic content and select Attachments Attachment id.

    Attachments Attachment id

It displays For each block for attachments.

block for attachments

Configure Azure Blob Storage destination

Next, click on Add an action. In the next step, we define the target storage location for storing these email attachments. In this demo, my destination storage in an Azure blob container. We already configured the input container for storing these files. Therefore, search for Azure Blob Storage.

Configure Azure Blob Storage destination

To upload a file into the Azure blob, select the Create blob from the actions list.

Create blob

It automatically lists the azure storage account with the resource group and location. If you want a different storage account, you can enter the details manually as well.

the resource group

Select your azure storage account and enter a connection name, as shown below.

enter a connection name

It asks for the following configuration of creating the blob action item:

  • Folder path: Click on the folder icon and select the container to save the email attachment. Here, we want to save these files into the /input container
  • Blob Name: In this configuration, we specify the blob container file format. For this purpose, I use the dynamic content option – Attachment Name

    Click on the blob name text box and click on Add dynamic content and select Attachment Name

  • Blob content: In the blob content, select Attachment Content from the dynamic content.

creating blob action

Let’s execute this azure logic app to validate things are working fine as expected. Until now, we implemented the following logic.

  • When a new email arrives in specified Outlook.com id, and it has a subject – Azure Data Import CSV, the logic app saves its attachment into an azure blob storage container

For demonstration purposes, I created the following CSV file.

Sample data file

Click on Run in the logic apps designer.

waits for a new email

It starts the workflow and waits for a new email in the Inbox.

C:\Users\u369656\AppData\Local\Microsoft\Windows\INetCache\Content.MSO\9B8092EA.tmp

Before we send an email, verify that no files exist in the input azure blob storage.

verify that no files exist

Create an email with the subject line that we specified during logic app configuration and send it to the configured email address. Here, you can see the email I sent with the Products.csv file as an attachment.

Create an email

We received the email in my configured email Inbox.

configured email Inbox.

We already started the logic app, but it was waiting for the new email in the Inbox. It detects the email and is processed with the following conditions.

Create blob

Refresh the storage container, and you have the Product.csv file uploaded in it by the logic app workflow.

Refresh the storage container

We have implemented the following task in the Logic app:

  • Detect an email in the Inbox with a specific subject line and having an attachment
  • The logic app retrieves the email attachment and saves it into the azure blob storage

Send email when a logic app uploads a file into an azure storage container

Suppose we wanted to get an acknowledgment or confirmation email once the azure logic app uploads a file into the storage container. In this way, you come to know about any file it has processed.

For this purpose, click on New item and select Outlook.com. In the task, search for the send an email (V2) task.

Send email  when logic app uploads a file

Click on Send an email (V2) and define a subject, body and recipient email address. I used the CONCAT string function for the subject and body to give the file name in the subject and body date.

concat(‘Products_’,formatDatetime(utcNow(),’yyyy’), ‘-‘, formatDatetime(utcNow(),’MM’), ‘-‘,formatDatetime(utcNow(),’dd’),’.csv’)

Click on Send an email(V2)

Mark email as Read once Azure logic app processed it

Let’s consider an additional requirement. Once the logic app uploaded an email attachment into the blob container, sends a confirmation email, the email should be marked as READ. For this purpose, select the Mark as read action from the Outlook.com task.

Mark email as Read

In the Mark as read action configuration, we pass the dynamic content Message Id input as shown below.

pass the dynamic content

Workflow

Save the logic run app and click on Run Trigger.

click on Run Trigger.

Once we send the email to a specified email address, it uploads an email attachment into the storage container, sends the email acknowledgment and marks an email as Read as you can see in the following screenshots.

  • Storage container:

    Storage container validate

  • Email content:

    Email content validate

  • Email is marked as Read:

    Email is marked as Read:

Conclusion

This article uses Azure Automation using the Azure Logic App to detect a new email with a specific subject, extract an attachment, and save it into the Azure storage container. We also configured the email confirmation once the upload is done. In the next part of the article, we will import this CSV file into Azure SQL Database using the BULK INSERT statement.

Table of contents

Azure Automation: Export Azure SQL Database to Blob Storage in a BACPAC file
Azure Automation: Create database copies of Azure SQL Database
Azure Automation: Automate Azure SQL Database indexes and statistics maintenance
Azure Automation: Automate Pause and Resume of Azure Analysis Services
Azure Automation: Automate data loading from email attachments using Azure Logic Apps
Azure Automation: Building approval-based automated workflows using Azure Logic Apps
Azure Automation: Auto-scaling Azure SQL database with Azure Logic Apps
Azure Automation: Analyzing Twitter sentiments using Azure Logic Apps
Azure Automation: Use Azure Logic Apps to import data into Azure SQL Database from Azure Blob Storage
Azure Automation: Publish LinkedIn posts and tweets automatically using Azure Logic Apps
Azure Automation: Export Azure SQL database data to CSV files using Azure Logic Apps
Azure Automation: Translate documents into different languages using Cognitive Services with Azure Logic Apps
Azure Automation: Azure Logic Apps for face recognition and insert its data into Azure SQL Database

Rajendra Gupta
Azure, SQL Azure

About Rajendra Gupta

As an MCSA certified and Microsoft Certified Trainer in Gurgaon, India, with 13 years of experience, Rajendra works for a variety of large companies focusing on performance optimization, monitoring, high availability, and disaster recovery strategies and implementation. He is the author of hundreds of authoritative articles on SQL Server, Azure, MySQL, Linux, Power BI, Performance tuning, AWS/Amazon RDS, Git, and related technologies that have been viewed by over 10m readers to date. He is the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups. Based on his contribution to the SQL Server community, he has been recognized with various awards including the prestigious “Best author of the year" continuously in 2020 and 2021 at SQLShack. Raj is always interested in new challenges so if you need consulting help on any subject covered in his writings, he can be reached at rajendra.gupta16@gmail.com View all posts by Rajendra Gupta

625 Views