This article uses Azure automation techniques for sending query results as a CSV file attachment in Azure SQL Database by using Azure logic apps.
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:
- Automate data loading from email attachments using Azure Logic Apps
- Analyzing Twitter sentiments using Azure Logic Apps
- Publish LinkedIn posts and tweets automatically using Azure Logic Apps
This article uses Azure Logic apps to send a query result to the specified recipient automatically.
- 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.
CREATE OR ALTER PROCEDURE GetmyResults
SELECT TOP 10 [ProductID]
If we run the stored procedure on the azure database, it returns results as shown below:
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.
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.
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.
You can click on Add new parameter and configure additional parameters such as time zone, start time, or specific minutes.
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).
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
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
Subject variable: Similarly, add another variable to store the subject of your sent by the Azure Logic Apps
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.
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
- 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
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
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.
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.
Now, connect to your email inbox and verify the email subject, body content, format.
The email also has an attachment – Products.csv. View the attachment, and it has query execution results.
In the logic app dashboard, you can view the Run’s history.
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.
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.