Rajendra Gupta
Email with attachment

Azure Automation: Export Azure SQL database data to CSV files using Azure Logic Apps

June 1, 2021 by

This article uses Azure automation techniques for sending query results as a CSV file attachment in Azure SQL Database by using Azure logic apps.

Introduction

In an on-premises SQL Server, we use SQL Server agent jobs for scheduling the task at a specific interval. For example, suppose you have a monitoring database, and you require the reports delivered to your email daily as per its defined schedule. For this purpose, you can use the database mail feature and schedule an agent job with the script to fetch query results, send it as an attachment using database mail and schedule it using SQL Server agent.

Azure SQL Database does not support the SQL Server agent and database mail feature. Therefore, if you have migrated to it, you need an alternative way of implementing your requirements.

The Azure Logic app is a serverless solution to define and implement business logic, conditions, triggers, workflows. In this series of Azure automation, we have implemented the following tasks using it:

This article uses Azure Logic apps to send a query result to the specified recipient automatically.

Note:

  • I assume that you have a basic understanding of azure logic apps. You can refer to articles (Toc at the bottom) for reference purposes
  • Create an Azure SQL Database with a sample database from the Azure portal

Implementation of logic to send query result as attachments in Azure SQL Database

To implement the workflow, we need to create a logic app in the Azure portal. Before we do that, let’s create a stored procure that fetches the top 10 records from a SQL table.

If we run the stored procedure on the azure database, it returns results as shown below:

Execute stored procedure

In this article, I require to create a CSV from the query result and send it over the email with a customized subject and body.

For this article, our final logic app workflow looks similar to the following screenshot.

logic app workflow

Let me explain each step for implementing the logic app.

Step 1: Recurrence

First, navigate to the Azure portal and create a new logic app. It lists a few useful templates. Select the blank logic app template and search for a schedule connector.

Recurrence

Now, we select Recurrence and specify the interval. Here, we specify the schedule. For example, below, we configure it to run daily at 11 AM.

specify the interval

You can click on Add new parameter and configure additional parameters such as time zone, start time, or specific minutes.

Add new parameter

You can configure one-time or recurring execution of the logic app workflow using the scheduler.

Step 2: Execute stored procedure (V2)

In this step, we want to execute a SQL stored procedure in the Azure database that retrieves results. Click on New Step and add SQL Connector. Provide the server, database name, and credentials for database authentication.

Once the connection details are specified, in the SQL Server connector action, add Execute stored procedure and select the procedure name from the drop-down. As stated earlier, my procedure name is [dbo].[GetmyResults].

If you use multiple SQL Server connections in a logic app, you can verify the current connection at the bottom of executing the stored procedure (V2).

Execute stored procedure (V2)

Step3: Define Variables

Here, we define a few variables that contain information for sending the email. Search for a variables connector and Initiate variable.

  • Filename Variable: In this variable, we store the CSV file attachment name. To define the variable, add the name, type as a string, and specify its value. Here, we give the variable value as Products.csv

    Define Variables

  • ToEmailAddress variable: Click on add new step, variables connector and Initialize variable. Here, define the recipient’s email address. We can directly specify an email address in the email configuration, but it is good to store them in a variable. If you use an email address multiple times in a logic flow app and modify it, you can quickly change the variable value without looking for each step

    ToEmailAddress variable

  • Subject variable: Similarly, add another variable to store the subject of your sent by the Azure Logic Apps

    Subject variable

Step 4: Create a CSV table

In step 2, we specify a stored procedure for execution in an Azure SQL database. We do not want these results in the email body. It should come as a CSV file attachment. Therefore, in the new step, search for Data Operations and select Create CSV table Under Choose an action.

Create a CSV table

It has two configuration options.

  • From: Here, specify the input that needs to be converted into a CSV file. Select the ResultSets Table1 from the dynamic content

    dynamic content

  • Columns: By default, its value is set to Automatic. It creates the CSV table columns automatically based on the input items. If you want to use custom headers and values, select custom from the drop-down and do the manual configurations.

Step 5: Send an email

The Azure Logic app supports various email clients such as O365, Outlook.com, Gmail. In this article, we configure the Outlook.com connector. Search for Outlook.com, enter your credentials, provide permissions to access the outlook.com profile by the azure logic apps.

In the actions menu, click on Send an email (V2). In the Send an email (V2), do the following configurations:

  • Body: Enter the text you wish to appear in the email body. In the body, you have formatting options such as
    • select font size and type
    • Bold, Italic, or Underline text
    • Bullet points or numbered list
    • font color
    • Link\Unlink

    You cannot expand the mail body in the send an email (V2) configuration. Therefore, it is best to write the content in either email or Microsoft Word and paste it here.

  • Subject: We already define a subject in a variable. Therefore, add the variable from the dynamic content
  • To: Similarly, add the variable [ToEmailAddress] for the recipient’s email address
  • Attachments Content -1: For the attachment, select the Output from the dynamic content
  • Attachment Name -1: In this section, we define the email attachment file name. We already define an attachment file name; therefore, add the variable filename here

Send an email

Step 6: Run the azure logic app and validate the email

Save the logic app and click on Run to trigger an email with query output as a CSV attachment. As shown below, all steps are executed successfully for the logic app.

Run the azure logic app

You can expand any step from the execution and retrieve the details. For example, expand the send an email(V2), and you can view email content such as Email address, subject and body.

retrieve the details

Now, connect to your email inbox and verify the email subject, body content, format.

connect to your email inbox

The email also has an attachment – Products.csv. View the attachment, and it has query execution results.

Email with attachment

In the logic app dashboard, you can view the Run’s history.

logic app dashboard

If you want to explore more on a particular run history, click on it and display workflow details for that specific Run. Suppose a few of your logic app execution takes longer so you can view its history and dig into each component to investigate the reason or delay or failure.

run history

Conclusion

This article explored Azure logic apps for sending the email of Azure SQL Database query execution results through an email attachment. Similarly, you can configure your logic, query, or stored procedure outputs using a formatted email to the required recipients.

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

441 Views