Steve Spencer's Blog

Blogging on Azure Stuff

Migrating Azure Scheduled Web Jobs to Logic Apps

If you have scheduler jobs running in Azure you may have received an email recently stating that the scheduler is being retired and that you need to move your schedules off by 31st December 2019 at the latest and you also will not be able to view your schedules via the portal after 31st October.

This is all documented in the following post:  https://azure.microsoft.com/en-us/updates/extending-retirement-date-of-scheduler/

There is an alternative to the Scheduler and that is Logic Apps and there is a link on the page to show you how to migrate.

I’m currently using the scheduler to run my webjobs on various schedules from daily and weekly to monthly. Webjobs are triggered by using an HTTP Post request and I showed how to set this up using the scheduler in a previous post :

Creating a Scheduled Web Job in Azure

I will build on that post and show how you can achieve the same thing using Logic Apps. You will need the following information in order to configure the Logic App: Webhook URL, Username, Password

You can find these in the app service that is running your webjob.  Click “Webjobs”, select the job you are interested in the click “Properties”. This will display the properties panel where you can retrieve all these values.

image

Now you need to create a Logic App. In the Azure Portal dashboard screen click “Create a Resource” and enter Logic App in the search box, then click “Create”

image

Complete the form and hit Create

image

Once the resource has created you can then start to build your schedule. Opening the Logic App for the first time should take you to the Logic App Designer. Logic Apps require a trigger to start them running and there are lots of different triggers but the one we are interesting in, is the Recurrence trigger

image

Click “Recurrence” and this will be added to the Logic App designer surface for you to configure

I want to set my schedule to run at 3am every day so I select frequency to be Day and interval to be 1, then click “Add New Parameter”

image

Select “At these hours” & “At these minutes”. Two edit boxes appear and you can add 3 in the hours box and 0 in the minutes box. You have now set up the schedule. We now need to configure the Logic App to trigger the web service. As as discussed above we can use a web hook.

All we have in the Logic App is a trigger that starts the Logic App at 3am UTC, we now need to add an Action step that starts the web job running.

Below the Recurrence box there is a box called “+ New Step”, click this and then search for “HTTP”

image

Select the top HTTP option

image

Select POST as the method and Basic as Authentication, then enter your url, username and password

The web job is now configured and the Logic App can be saved by clicking the Save button. If you want to rename each of the steps so you can easily see what you have configured then click “…” and select “Rename”

image

You can test the Logic App is configured correctly by triggering it to run. This will ignore the schedule and run the HTTP action immediately

image

If the request was successful then you should see ticks appear on the two actions or if there are errors you will see a red cross and be able to see the error message

image

If the web job successfully ran then open the web job portal via the app services section to see if your web job has started.

If you want to trigger a number of different web jobs on the same schedule then you can add more HTTP actions below the one you have just set up. If you want to delay running a job for a short while you can add a Delay task.

If you want to run on a weekly or monthly schedule then you will need to create a new Logic App with a Recurrence configured to the schedule you want and then add the HTTP actions as required.

The scheduler trigger on the Logic App will be enabled as soon as you click Save. To stop it triggering you can Disable the Logic App on the Overview screen once you exit the Designer

image

Hopefully this has given you an insight in to how to get started with Logic Apps. Take a look at the different triggers and actions as see that you can do a lot more than just scheduling web jobs

Using Azure Logic Apps to Import CSV to SQL Server

When Logic Apps first came out I wrote a blog post explaining how to convert a CSV file into XML.A lot of this is still relevant, especially the integration account and the schemas and maps that are in my github repo. This post will show how Logic Apps are now even simpler to use with flat file decoding and also show how to insert the CSV data into a SQL server. The SQL part of the blog was adapted from this post: https://pellitterisbiztalkblog.wordpress.com/2016/11/14/upload-flat-file-on-azure-sql-database-using-azure-logic-app/

Logic Apps has evolved since I last wrote about this topic and you now no longer need to create a function to transform our csv to xml.

clip_image001

The Transform XML connector is used now with the same maps we used in the previous post

In order to add the individual rows to the database there are a number of things you need to do. We will use an XML schema mapping in a stored procedure to extract the data from the transformed xml.

In your SQL database you will need to add a stored procedure, table and an XML schema, The SQL Scripts to create the table, stored procedure and xml schema have been added to the github repo. The stored procedure takes the xml file that has been transformed and uses the xml schema to extract the firstname, middlename and surname from the xml and then store the data in the employees table. In the logic app you need to add a SQL server connector and configure the connection to your Azure SQL database and also add in the stored procedure with the parameter as the output from the Transform XML.

image

The only other thing I needed to do to get this working was to remove the first row of the csv file as it contained the header fields and I didn’t want that inserted into the database.

image

The “length” expression is:  length(variables('csvdata'))

image

The “indexOf” expression is: indexOf(variables('csvdata'),'\r\n')

However if you add this in the editor the back slash will be delimited and you will end up with \\r\\n which will not work. To fix this you will need to click the View Code button, search for the \r\n and remove the extra back  slash

The “substring” expression is: substring(variables('csvdata'),add(variables('firstnewlineposition'),2),sub(variables('csvlength'),add(variables('firstnewlineposition'),2)))

The trigger for my Logic App was when a new file was added to OneDrive, so click the run button and then drop a file into the configured OneDrive location and the csv entries should be added to your database.

Diagnosing Azure Logic App Faults

In my previous post I showed you how to create a flat file decoding Logic App. When I first tested this I used URL generated when I first saved the Logic App to create a post command using the compose feature of fiddler.

image

The body of the message contained a single cvs row that I wanted to debug and I was hoping to see the response containing the xml representation of the csv file.

When I submitted the request, fiddler displayed an 502 error – Bad Gateway

image

This wasn’t very helpful and I needed to understand what had gone wrong.

Navigating to the Logic App in the Azure Portal I noticed that the Overview blade showed the runs for the Logic App

image

Clicking the failed run opened a new blade in the portal which showed my Logic App and it failing at the Flat File Decoding stage

image

Clicking the Flat File Decode caused it to expand and show me a more detailed (and useful) error

image

The error told me that the Flat File Decode couldn’t find the CR/LF at the end of the line. As I’d put a single line of text and no CR/LF the decoding failed.

I repeated the POST, this time with the CR/LF at the end of the body and the Logic App worked correctly and the correct xml representation of the csv was displayed in the body of the response.

Processing a flat file with Azure Logic Apps

[Update: 5th Aug 2018 – This post is still relevant especially the integration account, schemas and maps and I  have written a new blog that builds on this one and integrates into SQL  -  Using Azure Logic Apps to Import CSV to SQL Server]

A lot of companies require the transfer of files in order to transact business and there is always a need to translate these files from one format to another. Logic Apps provides a straight forward way to build serverless components that provide the integration points into your systems. This post is going to look at Logic apps enterprise integration to convert a multi-record CSV file into and XML format. Most of the understanding for this came from the following post:

https://seroter.wordpress.com/2016/09/09/trying-out-standard-and-enterprise-templates-in-azure-logic-apps/

Logic Apps can be created in Visual Studio or directly in the Azure Portal using the browser. Navigate to the azure portal https://portal.azure.com click the plus button at the top of the right hand column, then Web + Mobile then Logic App

clip_image002

Complete the form and click Create

clip_image003

This will take a short while to complete. Once complete you can select the logic app from you resource list to start to use it.

If you look at my recent list

clip_image005

You can see the logic app I’ve just created but you will also see my previous logic app and you will also notice that there is also an integration account and an Azure function. These are both required in order to create the necessary schemas and maps required to translate the CSV file to XML.

The integration account stores the schemas and maps and the Azure function provides some code that is used to translate the CSV to XML.

An integration account is created the same way as a logic app. The easiest way is to click on the plus symbol and then search for integration

clip_image007

Click on Integration Account then Create

clip_image009

Complete the form

clip_image010

Then Create. Once created you can start to add your schemas and maps

clip_image012

You will now need to jump into Visual Studio to create your maps and schemas. You will need to install the Logic Apps Integration Tools for Visual Studio

You will need to create a schema for the CSV file and a schema for the XML file. These two blog posts walk you through creating a flat file schema for a CSV file and also a positional file

I created the following two schemas

clip_image014

clip_image016

Once you have create the two schemas you will need to create a map which allows you to map the fields from one schema to the fields in the other schema.

clip_image018

In order to upload the map you will need to build the project in Visual Studio to build the xslt file.

The schemas and map file project can be found in my repository on GitHub

To upload the files to the integration account, go back to the Azure portal where you previously selected the integration account, click Schemas then Add

clip_image020

Complete the form, select the schema file from your Visual Studio project and click OK. Repeat this for both schema files. You do the same thing for the map file. You will need to navigate to your bin/Debug (or Release) folder to find the xslt file that was built. Your integration account should now show your schemas and maps as uploaded

clip_image021

There’s one more thing to do before you can create your logic app. In order to process the transformation some code is required in an Azure Function. This is standard code and can be created by clicking the highlighted link on this page. Note: If you haven’t used Azure Functions before then you will also need to click the other link first.

clip_image023

clip_image025

This creates you a function with the necessary code required to perform the transformation

clip_image027

You are now ready to start your logic app. Click on the Logic App you created earlier. This will display a page where you can select a template with which to create your app.

clip_image029

Close this down as you need to link your integration account to your logic app.

Click on Settings, then Integration Account and pick the integration Account where you previously uploaded the Schemas and Map files. Save this and return to the logic app template screen.

Select VETER Pipeline

clip_image031

Then “Use This Template”. This is the basis for your transformation logic. All you need to do now is to complete each box.

clip_image032

clip_image034

In Flat File Decoding & XML Validation, pick the CSV schema

clip_image035

In the transform XML

clip_image036

Select the function container, the function and the map file

All we need to do now is to return the transformed xml in the response message. Click “Add an Action” on Transform XML and search for Response.

clip_image038

Pick the Transformed XML content as the body of the response. Click save and the URL for the logic app will be populated in the Request flow

clip_image039

We now have a Request that takes the CSV in the body and it returns the XML transform in the body of the response. You can test this using a tool like PostMan or Fiddler to send in the request to the request URL above.

There is also a test CSV file in my repository on GitHub which can be used to test this.

My next post covers how I diagnosed a fault with this Logic App