Azure Logic App to Extract and Save Email Attachments


By:   |   Updated: 2020-07-23   |   Comments   |   Related: More > Azure

Problem

You have a request to automate a file delivery from a data provider for import into a SQL Server database. Simple enough, but in this case the provider does not support retrieving the file from them and will only supports delivery via email attachment(s) or SFTP. The first thought was to provide an external facing SFTP server on our network or even a small possibly Linux VM where it already has a native SFTP server, but we really couldn't justify it for just one feed. Next, we thought, would a cloud service work for us?

Solution

The answer is yes, a cloud service could work for this. So, let's build an Azure Logic App.

First, here are the pieces we'll be working with:

  • On-premises data gateway for Azure Logic Apps
    • The gateway works as an encrypted conduit between the cloud and your OnPrem server's file system
  • Microsoft 365 email
    • We're using a dedicated email address for the file drops
  • Azure Login App
    • Azure cloud service used to automate the process
  • File system
    • Simply a directory on an On-Premises Windows server

On-premises data gateway for Azure Logic Apps

We'll presume you don't already have an on-premises data gateway installed so we'll step through the process. If you have an existing gateway you can skip to the Creating the Logic App section.

The Microsoft Docs install document I followed can be found here: Install on-premises data gateway for Azure Logic Apps

  1. Download and the installer from here: Download on-premises data gateway
  2. Run GatewayInstall.exe as an administrator on the machine you're installing it on
  3. Confirm or change install directory
  4. Accept terms of use
  5. Install
Start gateway install
  1. Enter email to associate with gateway
  2. Sign In
Enter email
  1. Enter email
  2. Next
Sign in
  1. Enter password
  2. Sign in
Enter password
  1. Select Register a new gateway on this computer radio button
  2. Next
Register gateway
  1. Name gateway
  2. Enter and confirm recovery key
  3. Store recovery key in your password manager in case you need to change the location, move, recover, or take over a gateway installation
Create gateway
  1. Close
Gateway is ready

Creating the Logic App

Log in to the Azure Portal.

  1. Create a resource
Create a resource
  1. Enter Logic App in search box
  2. Choose Logic App
Logic App
  1. Create
Create Logic App
  1. Choose Subscription
  2. Choose Resource Group – or create new
  3. Give it a name
  4. Choose Location
  5. Review and Create
Configure Logic App
  1. Review
  2. Create
Review and Create Logic App
  1. Click Go to resource
Go to resource
  1. Scroll down to Blank Logic App template
Templates
  1. Enter email in search box
  2. Office 365 Outlook
  3. When a new email arrives (V3) (latest as of this writing)
When a new email arrives (V3)
  1. Sign in
Sign in to O365
  1. Connect with email account you're monitoring
Enter email account
  1. Password
  2. Sign in
Enter password
  1. Folder = Inbox
  2. Importance = Any
  3. Only with Attachments
  4. Include Attachments = Yes (why process if no attachments(s)
  5. Any additional filters under 'Add new parameter' dropdown
  6. New step
Configure step
  1. 'Office 365 email get attachment' in the search box
  2. Office 365 Outlook
  3. Get Attachment (V2)
Choose action
  1. Add dynamic content
  2. Message id in the search box
  3. Message Id
Message Id
  1. Add dynamic content
  2. Attachment in the search box
  3. Attachments Attachment Id
  4. New step
Attachment Id
  1. Add an action
Add an action
  1. Enter file system in the search box
  2. File System
  3. Create file
Create file
  1. Name Connection
  2. Root folder
  3. DOMAIN\account for Windows account that has write permissions on C:\AttachmentTest
  4. Password
  5. Subscription
  6. Use the On-premises Data Gateway we created
  7. Create
Create file system connection
  1. Folder path - \ is root of C:\AttachmentTest
  2. Add dynamic content
  3. Search for attachment
  4. Attachments Name
Configure file creation
  1. Add dynamic content
  2. Attachments Name
Configure file creation
  1. Save
Save

I'm testing this with 2 .csv files, C:\temp\sample1.csv and C:\temp\sample2.csv.

The following PowerShell is helpful for testing the Logic App. Especially, if you need to do some debugging. Just configure the variables and run it.

$PSEmailServer='smtp.myserver.com'                            # smtp server, presuming credentials aren't needed to connect
$Attachments = @("c:\temp\sample1.csv","c:\temp\sample2.csv") # if testing with more than 1 file, use array as shown
$From = "[email protected]"                                   # sender - just needs to be in the form [email protected]    
$To = "name @mydomain.com"                                    # email to send to    
$Subject = "test"                                             # subject    
 
Send-MailMessage -From $From -To $To -Subject $Subject -Attachments $Attachments

And here are our files.

I was skeptical that there could be slight differences in the files where I had to create and populate them rather than a simple copy but file compares between the source and target files show they're the same.

Compare files

Add functionality to create a 'trigger' file

Now that we've built our initial Logic App it's easy to add on to. We had an additional requirement to deliver an empty file named transfer.done that will be used as the trigger for the custom import.

  1. New Step
New step
  1. Create file
Create file
  1. Folder path
  2. File name
  3. Added a string with a comment on the file's purpose in case someone should open it out of curiosity just because it's a required field.
Configure file

Mark email as read

And finally, I thought it would make sense to mark the email as read after the process completed. As the mailbox will be used exclusively to receive file attachments it would be helpful if troubleshooting the logic flow. An email still marked as unread would indicate the processing did not complete successfully or did not meet the from or attachment criteria.

  1. New step
New step
  1. Mark as read in the search box
  2. Mark as read or unread (V2)
Mark as
  1. Add dynamic content
  2. Id in search box
  3. Message Id
Identify email to mark
  1. Add new parameter dropdown
  2. Mark as
Mark as
  1. Read
Mark as read
Next Steps

I hope you find Azure Logic Apps as interesting and useful as I do. Here are some more tips on uses of and how to use Azure Logic Apps:



Last Updated: 2020-07-23


get scripts

next tip button



About the author
MSSQLTips author Joe Gavin Joe Gavin is from Greater Boston. He has held many roles in IT and is currently a SQL Server Database Administrator.

View all my tips
Related Resources





Comments For This Article





download


Recommended Reading

Adding Users to Azure SQL Databases

Getting Started with Azure Blueprints

Connect to On-premises Data in Azure Data Factory with the Self-hosted Integration Runtime - Part 1

Azure Data Factory vs SSIS vs Azure Databricks

Continuous database deployments with Azure DevOps





get free sql tips
agree to terms


Learn more about SQL Server tools