다음을 통해 공유

Azure: Daily Reports using Logic Apps

  • 아티클

Introduction

Working on-premises SQL having many advantages like you have the full administrative rights over a dedicated SQL Server instance, we can run scheduled Jobs using SQL Agent Job. Speaking of SQL Agent Job, I personally a huge fan of this. We can do a lot and lots of stuff like schedule jobs, view the job history, Run manual jobs, Sending Emails etc., 

But when coming into DBaaS (Database as a Service) which is nothing but Azure SQL Database. There are no such options like Agent Jobs. Since it's a Paas like service, we just own our code and not the Infrastructure and Services (like Agent Jobs).

Most of them have a requirement to send daily reports to our some concerned person/admin via Email with the information about some details which are updated/created on that day (Let say for an instance it is user details ). If we have a traditional on-premises SQL then it will a simple task by creating a Stored Procedure, then run the SP using Agent Job for a particular time period. But for the cases like Azure SQL Database, we need to find other approaches**.**

In this article, we will see gonna see how this can be achieved easily and efficiently using Logic Apps.

Prerequisites

End Goal

Our End goal is to send daily Reports via E-mail by gathering data(Executing SP) from an Azure SQL Database efficiently.  To achieve our goal, our design should be more easy to manage, less cost computation, managing alerts/logs for troubleshooting purpose.

Logic App Flow

The working model is very simple and easy to understand, We are just going to use 4 task.

  1. Recurrence Task
  2. Execute Store Procedure Task
  3. Create CSV table Task
  4. Send an Email Task

Full Designer View

The below image is the full design view of our final Logic App. We will see the detailed task one-by-one in the below topics.This view gives an overview idea of how the Logic App looks like.

Recurrence Task

This is where we actually set the Schedule to run our logic apps at the beginning of execution. I've scheduled it to run daily once at 1:00 AM (IST)

 

Execute Stored Procedure Task

This is the task where we will connect to our Azure SQL Database and call the Stored Procedure we need.

E.g,

SQL Queries

CREATE TABLE  USERDETAILS (ID INT  IDENTITY(1,1),NAME  VARCHAR(30),AGE INT);
GO
INSERT INTO  USERDETAILS VALUES  ('JAYENDRAN',25),('TESTING',26);
GO
CREATE PROCEDURE  USP_USERDETAILS
AS
SELECT * FROM USERDETAILS

Create CSV table Task

In this task, we will dump our result set from the SP into a CSV table, which we will attach into our report mail in the next task

Send an Email Task

This will be the final stage of our Logic App, we will configure the Body, Subject, To-Email- Admin Email and the attachment (CSV file the from the previous task)

For the Attachment Name, I just used the concat operations

Attachment Name - 1

concat('userdata','.csv')

Running the Logic App

After creating all the task and connecting together as shown in the below figure, we are ready to test our Logic App. After clicking the Run button our Logic App will start running the Task one after another. We can see the time taken to complete each task in the below figure. (Which is almost less than a Second)

  • If the task is successfully executed it will be marked as a Green Tick at the top right Corner along with the time taken to complete that task
  • In case of any error will be marked as a Red Exclamatory at the top right Corner along with the time taken to complete that task
  • Logic Apps is inbuilt with the Retry logic, so in case of any error instead of changing into error status directly, it will retry up to 3/4 times.

You can customize the error handling of Logic Apps too. Handling Error's in Logic Apps is given below (See Also)

Report Email

It's time to verify the mailbox. As expected I got the below Email, The Subject, the content of Body, the attachment came correctly as per we configured in the send an Email Task

UserData.csv

Finally, we got our Reports in the CSV format, let try to verify that the data are present in the file. The file having the below contents which are the expected result.

ID,NAME,AGE
1,JAYENDRAN,25
2,TESTING,26

Hurray! We finally achieved our goal easily and efficiently. 

Summary

Using Logic Apps(Serverless App) we can easily send daily reports by executing Store procedure from Azure SQL Database. This article will give you a simple flow design to implement it, we can also design with a complex workflow depends on the nature of the reports.

See Also