Logic Apps: Saving files sent via Emails into Blob Storage

Using Microsoft Logic Apps we save files to Azure Blob storage that are sent to an Outlook email inbox, using Azure SQL database tables to provide appropriate file names. I will discuss this process in the blog.

Consider we have a scenario in which every week, 3 CSV files are received from separate email addresses containing a product catalogue, sales information and one containing employee details. The senders’ address is subject to change and occasionally, emails contain multiple files e.g. one week multiple files containing product information are sent in one email.

We want to be able to:

a) Check the inbox for any new emails.

b) Save any new files to Blob Storage whilst:

• • Maintaining a historic record of the files.
  • Having appropriate folder and naming conventions to enable location and to allow event-based triggers in ADF. 

c) Be notified if an email sender is not in the list of expected senders.

d) Be flexible enough to add new email senders or to receive a 4th type of file in the future.

Azure logic apps provide the perfect interface for a quick solution to this problem via its simple GUI:

” Azure Logic Apps is a cloud-based platform for creating and running automated workflows that integrate your apps, data, services, and systems. With this platform, you can quickly develop highly scalable integration solutions for your enterprise and business-to-business (B2B) scenarios.”
Logic Apps provide 100’s of managed connectors to easily and securely access and run operations on various data sources including:

• Azure services such as Blob Storage.
• Office 365 services such as Outlook, SharePoint and Excel.
• Database servers.

To communicate with any service endpoint, run your own code, organize your workflow, or manipulate data, you can use built-in triggers and actions, which run natively within the Azure Logic Apps service. Built-in actions include Condition, For each, call Azure Functions, web apps or API apps hosted in Azure. Read more about logic apps here: Overview for Azure Logic Apps – Azure Logic Apps | Microsoft Docs

Worked Example 

Connections

Most connectors require that you first create a connection before using a connector’s triggers or actions. To create a connection from within a logic app workflow, you have to authenticate your identity with account credentials and sometimes other connection information.

For example, before we can access and work with an Office 365 Outlook email account, you must authorize a connection to that account.

Similarly, to connect to a SQL Server you must first provide connection details to the server and in the case of an on-premise server, create a gateway.

However, in our example, we are using an Azure SQL database so this isn’t necessary. For our logic app, we also need to create a connection to Azure Blob storage, using an access key.
These connections can then be managed via ‘API connections’ under the Development tab.

When a new email arrives

In our example, the trigger for the logic app is a new email being received in the Outlook inbox. You set how often the inbox is scanned, and can specify other parameters such as only being triggered from certain email addresses and only consider emails with attachments.
Importantly, we must set the ‘Include Attachments’ parameter to ‘yes’ (this is not a default behaviour and resulted in my app initially not working).

Connect to SQL email lookup table

Using the SQL server connector and the ‘Get row‘ action, we connect to a SQL email lookup table. Importantly, the email in the Lookup.Email table is a primary key, so we can retrieve a specific row from this table. We are interested in the FileID, which is a foreign key in Lookup.Email and a primary key in Lookup.FileMetadata.

Any of these columns can now be referenced within the logic app, so any variables you want to be referenced in your logic app can therefore be contained and maintained within SQL tables. Since we want to allow for multiple different senders to send a specific file, two lookup tables are needed.

This means two email addresses can be mapped to the same FileID in Lookup.Email, we can then maintain all metadata associated with that FileID in a separate table.

Configure run after 

Logic apps allow us to run different activities depending on whether preceding activities are successful, have timed-out, are skipped or have failed. This useful feature allows us to carry out appropriate action if the email sender is not found in our email lookup. Here, we chose to send an email alert, including the email sender which is not mapped and the attachment.

This highlights another useful aspect of logic apps: in our example, should we decide to add the missing email to our lookup table, we can do this and then re-submit the initial, failed run. On the ‘Runs history’, we can resubmit any logic app run, which will create a new instance of the Logic App by firing an identical trigger message as the original run. This feature is also incredibly useful during development, as you do not have to manually recreate the trigger (something I wish I knew about a lot sooner).

Create Year and Month variables 

Later, we will want to save the files in Blob Storage in appropriate year and month folders. Surprisingly, there is no built in function to do this, so we use the substring and utcNow functions to initialize a year and month array variable, from which we can later reference the year and month explicitly.

For Each attachment

The logic app then iterates through every attachment contained within the email. Using the ‘FileType’ (sales, people, product etc.) which is accessible through the second SQL lookup and our year and month variables, we can save the file in the appropriate directory.

We give the file the name of the file type along with a timestamp and the CSV extension.

Criticisms and limitations 

By default, ‘For each’ iterations run in parallel. Variables must be initialised at a global level (outside of loops), which in itself is unideal. This is compounded by the fact that there is no concept of a Mutex in Logic Apps. If a variable is then set within a loop, multiple threads can do this at one time – leading to strange results, particularly when the number of concurrent runs is high. To mitigate against this problem you can adjust the settings and allow for concurrency control, setting the degree of parallelism to 1.

It is worth mentioning another limitation of Logic Apps (particularly within for each blocks): the GUI that makes the logic app so attractive and welcoming to users from a non-technical background is not as effective as it could be. The inability to drag actions to to the top of a for each block means extra care and thought must be made before creating the logic app or you may face having to delete and replicate your previous work.

Also, the interactive dynamic content built does not provide full functionality. For example, there is no option to click the year/month array within the folder path setting in the picture containing the create blob activity, from which you could access an item in this array. Hence, for users who may not be able to build the dynamic content without graphical aid, it is easier to set these variables outside the loop, where the dynamic content builder is more functional.

Conclusion

This example was just a brief introduction to how logic apps can provide quick solutions to various problems. The growing set of connectors gives logic apps a wide set of use-cases. As just one example for this use case, the Cloudmersive API would allow files sent in as ZIP’s to be unzipped, or convert files sent in as different filetypes. Read Using ADF V2 Activities in Logic Apps – Adatis on how to provide input parameters to ADF from Logic Apps.

Repository link: AlexOCrampton/LogicApp (github.com)

LinkedIn: Alex Crampton | LinkedIn