Workflow Orchestration with Azure Logic Apps to Move Data

Overview

In this video we look at how you can use Azure Logic Apps to build a workflow and use the built-in functions to load data to a SQL Server database.

Recommended Reading

• Start an Azure Logic App Workflow on Demand or by an Event
• How To Call Logic App Synchronously From Azure Data Factory
• Synchronously Refreshing a Power BI Dataset using Azure Logic Apps
• Transfer Files from SharePoint To Blob Storage with Azure Logic Apps

Video Transcript

Hello, everyone. Welcome to another video in the series “Moving Data around in Azure,” and in this episode of the series, we’ll talk about Azure Logic Apps, a code free environment to quickly create workflows to move data around. So my name is Koen Verbeeck. I work as a senior business intelligence consultant for AE. I write some tips for MSSQLTips.com; I have some certifications; I’m also a Microsoft data platform MVP for a couple of years now. And if you have any questions, just put them in the comments below or you can contact me through Twitter, LinkedIn or on my blog.

Right, so Azure Logic Apps. You can summarize them as business workflows without having to write any code. You can encode some sort of workflow without having to write any programming language or code — maybe some expressions, but overall it’s very code free. A workflow can be if someone arrives in your company, you can send an email, this email can be picked up by a mailbox and our apps and logic apps can pick these up. And they say, okay, they can run some PowerShell scripts that automatically create views in Azure Active Directory and add views to some groups and then a couple of other emails to HR notify, “Okay, these people will start on this day,” and so on. So this is a whole workflow you can build using Azure Logic Apps to automate certain tasks in your company.

There are lots of built-in connectors and actions available for you to choose from. So it can connect to a wide array of other products — mostly cloud products, of course — and you can do lots of stuff. You can connect to SharePoint, connect to a SQL server, to Azure SQL Server database, you can use Azure Data Factory Power BI, but also non Microsoft products and I will show you a couple of them in a demo. And Azure Logic Apps is basically the Azure version of Microsoft Flow, which was available in the Office 365 Suite, but is now renamed to Power Automate. So it’s part of the power families of Power BI. Power Automate, Power Bots and Power Apps and Power Automate now is also part of the Power family. So Azure Logic Apps is actually behind Power Automate so it shares a lot of the same functionality and connectors.

All right, so this is an example of an Azure Logic App and typically it’s triggered by something so it’s server-less and it’s typically event based. So something happens — a file arrives or, in this case, an HTTP request is received. That will refresh a data set in Power BI, it will wait some time, set some variable and it will loop until it has been made sure the data set in Power BI has been refreshed successfully. In that case, nothing happens. Or if there was a failure, it will send an email to a mailbox saying, “Okay, the refresh of this Power BI data set has failed.” So this is something you can implement in Azure Logic Apps and there is a tip on this on the MSSQLTips.com website.

All right, so Azure Logic Apps, as I mentioned — lots of available connectors, but in the case something is not available of certain product you can also extend this through custom connectors. So for example, API wants to access, you have its swagger file, then you can create an Azure Logic App custom connector and each API model can then be translated into an action that you can do in Azure Logic Apps. And there’s also a tip on this on the MSSQLTips.com on how to do this for the Power BI API because for the moment not every action from the API is supported in Azure Logic Apps. You can refresh a dataset but, for example, you cannot retrieve the status of the refresh. So in the example that I showed earlier, to do this, you need to use a custom connection that goes to the Power BI API. All right, and this is an example of such a custom connector. All of the actions from the API are imported into this custom connector for Power BI.

Let’s go quickly through a demo of an Azure Logic App. So we go to Azure so you can create one in the Azure portal and then here you can see you have a designer and here you can see an example or when an HTTP request is received and be sent, for example from Azure Data Factory. It will go to Azure Blob Storage, it will retrieve a JSON file containing top movies it will parse this JSON. That will execute the SQL query, so truncate the destination table for each row — and this is important. Otherwise, it tries to put the entire JSON file into one single row in your database table which is probably not what you want. So you need to shred this JSON file and then insert — yeah, execute an insert statement for each row of this JSON file.

So this is what we’re doing here and for each loop for we’re going to insert a row in this specific table. So this is an Azure Logic App example. Here you can see, for example, I’ll try to add a new step. Here you can see all of the connectors that are available in Azure Logic Apps — and there are lots of them. So you can — lots of Azure functions, of course, like Azure Key Vaults, Service Bus, functions but also Office Outlook and then you have Adobe, you have Apache and the list goes on and on. There’s so many tools and suites that you can access. There are just too much to mention. Just to show you how powerful Azure Logic Apps is. And all of this, this whole Azure Logic Apps — I didn’t have to write any single line of code.

Right, so when I run this — so fake the trigger of the HTTP. It will take a few moments to actually start. Okay here it says, “Okay, I have successfully launched the Logic App,” and now it’s already done and now it’s inserting, row by row, the data into the SQL server. So this is probably not the most performant option. It has not the best performance. Executing row by row is typically you want to avoid when inserting data into SQL server, so a better option would have been to just drop this into some certain location which you can access by Azure Data Factory for example, or you can pick the file in its whole and just dump it as its whole into a SQL server which will be much faster than inserting 250 rows individually. So if you go check my SQL server database and I select the table and connect my database you can see that it already has 245 rows inserted. So you will be pretty much done. So as you can see, it’s finished and it has 53 seconds to insert all of the data.

Another example of a Logic Apps — for example, checking a SharePoint file and when a new file is created — so instead of an HTTP trigger it monitors a certain SharePoint location and if something happens there then this Azure Logic App is triggered and it will list all of the files it will get the content and create an Azure blob store. So this is yet another example of something you can do in Azure Logic Apps and they can get very complex and it can build some logic and this is a screenshot from one of the tips where we refresh the Power BI data set and you can see you can have a whole conditional structure that you can build using Azure Logic Apps. I wouldn’t try to make your Logic Apps too complex, though, but for certain workflows Azure Logic Apps is a very good solution. All right, let’s go back to the slides.

So if you’re interested in more there are already some tips on the Azure Logic Apps on MSSQLTips website and most of the examples that I showed you during the demo are explained in detail in those tips. So thank you for listening. I’ll see you in the next video.