As part of development guides, it’s always a good idea to have a fallback plan and handle errors.
You can be 99.999% confident that your code won’t fail, but that 0.001% chance happens. “Anything that can go wrong will go wrong” – Murphy’s Law
And so, we resort to our very dear friend, Try-Catch.
In Logic Apps, it’s not exactly an out-of-the-box functionality, but it’s actually quite simple to achieve this and with a few steps. Also, there are multiple ways to catch your errors.
In this post, we will try two approaches:
Using a For-Each loop
and a Filter Array action.
Since I’ve started developing LAs, I’ve used the For-each loop approach but it had some flaws. It involved using a Parse Json to catch only the error message, but not all actions have the same schema.
So, the idea of the Filter array came to play. It’s actually quite easy as well and easy to maintain. You’ll find the same issue with the schemas, but it’s a faster approach.
Let’s dig in. I started to build a simple Logic App, just creating a couple of variables and an HTTP call that I know that will fail. I mocked the results to ensure the outcome is what I needed.
I’ve also built a scope, creating the Try block and a second scope to handle the Catch block. You’ll have to set the “Run after” properties to only trigger on errors, skips or timeouts, if not, it will run on success.
It will always relate to the previous scope.
The For-Each loop approach
Now we start to build our Error handler. I’ve chosen the For Each loop first because it was faster to create since I’m used to it and even have some templates for it as well.
The For Each action requires an array action to iterate on, which means we need to find one. The Scope isn’t a loop, so what will we relate it to?
Well, the scope might not be, but there are N actions inside it, so if you search in the expressions box or the documentation, you’ll find the “result” expression, which records every result of the contained actions within a given scope.
Now, remember, this will need to point to the action you want, but you will not have it in the Dynamic Content, you need to write it using the _ for spaces, because this expression handles the JSON node name like if you’re working in the back code.
Once you have this set, you just need to create a condition to check if the status of the action was “Failed“. Pretty simple.
If you test the execution, you’ll see that the loop is working and iterating the actions batch that the “result” expression returns.
I’m just returning the action outputs in the error string, which will contain the StatusCode, Headers and Body. It should help to diagnose a possible error.
Let’s try the Filter array now.
The Filter Array action approach
Similar to the For Each, we need to iterate through an action that contains child actions. We use the same “result” expression pointing to the same scope as the “From” property and choose “item()?[‘status’]” as the node to search for. Also, we only want the failed actions, so the node should be equal to “Failed“.
As for the error message, it’s a bit different from the For Each type. We’re still picking up the Outputs but we need to get the first action from the Filter array action.
The end result should be the same, as we’re picking up the same info as with the For Each loop.
Usually, an action will return a JSON record as the result of its execution. There are some fields that will always be present, like “Status” and “Tracking ID”. There’s no easy way to find this info, so you have to deconstruct one or more actions to find it. With the information you have now, you can get it from anywhere, you just have to use the “Result” expression.
Here you can see some fields in the Set var action I created and how the status is recorded. For tracking purposes, the execution engine records the begining and end timestamps as well as other useful data.
Now that you know how, it’s time to get working and make your Logic Apps sturdier and with a proper error handling.
I usually use the series A fish out of water when I want to write something that goes a little bit off-topic on my main blog topic: Enterprise Integration. But this time is different. This was kind of the first thing I thought when I saw this error happen since that didn’t make sense at all.
To contextualize better this reason and blog post, I have been testing the new Logic Apps (Preview) for a while, and if you already try it before, you will know that this new type of Logic Apps runs on top of Azure Function Runtime. This means that now you can run Logic Apps anywhere: in the cloud, on-premises, locally on your laptop, or wherever you need to.
One of the requirements necessary to have the full Logic Apps designer support in VS Code is the Microsoft Azure Storage Emulator 5.10 tool
Microsoft Azure Storage Emulator 5.10 tool – This tool is necessary to have the full Logic Apps designer support in VS Code. This tool will use a local Microsoft SQL Server Express LocalDB instance (you can also use a SQL Server instead) to emulate Azure storage services.
Everything was working properly for the last four months or more, however for no plausible reason today while I was trying to start the Microsoft Azure Storage Emulator I got this error:
Probing SQL Instance: ‘(localdb)MSSQLLocalDB’. Caught exception while probing for SQL endpoint. A network-related or instance-specific error occurred while discovering a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 50 – Local Database Runtime error occurred. Error occurred during LocalDB instance startup: SQL Server process failed to start. ) Number of SqlErrors Reported: 1 SqlError: System.Data.SqlClient.SqlError: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 50 – Local Database Runtime error occurred. Error occurred during LocalDB instance startup: SQL Server process failed to start. ) Could not find a LocalDB Installation. Probing SQL Instance: ‘localhostSQLExpress’. Caught exception while probing for SQL endpoint. A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified) Number of SqlErrors Reported: 1 SqlError: System.Data.SqlClient.SqlError: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified) No available SQL Instance was found. One or more initialization actions have failed. Resolve these errors before attempting to run the storage emulator again.
Cause
Again, I can’t find any plausible reason for this to start happend, except the fact that my machine installed some system updates.
Solution
I don’t know if all these steps are necessary or not, I was just simple frustrated about this situation because I need to present this topic in my upcoming sessions this week, so I didn’t test all the hypotheses available properly. Nevertheless, I managed to solve this issue by simply recreating my LocalDb instances and allowing the emulator to recreate his db files.
Here is the steps necessary to perform these tasks:
Get your current LocalDB instance name
<pre class="wp-block-preformatted">
sqllocaldb i
</pre>
Delete the existing LocalDB instance
If you feel more comfortable you can choose to stop the LocalDB instance before you delete
<pre class="wp-block-preformatted">
sqllocaldb d &lt;LocalDB instance>
</pre>
Create your new LocalDB instance with the same name or a different one.
<pre class="wp-block-preformatted">
sqllocaldb c &lt;LocalDB instance>
</pre>
and finally, you need to delete the following Azure Storage Emulator database files to allow the emulator to recreate db files on the first start.
Do you feel difficult to keep up to date on all the frequent updates and announcements in the Microsoft Integration platform and Azure iPaaS?
Integration weekly updates can be your solution. It’s a weekly update on the topics related to Integration – enterprise integration, robust & scalable messaging capabilities and Citizen Integration capabilities empowered by Microsoft platform to deliver value to the business.
Everyday we learn new things and when it comes to Logic Apps, we tend to learn even more, because it’s always shifting and new components are added. If we’re using ARM templates, the deployment brings out some challenges and with it, new things to learn (and lots of cute little things that make you want to bang your head against a brick wall).
Usually when we work with a CSV file we tend to keep the sorting according to the specification. It isn’t always alphabetical nor descending/ascending.
Sometimes, it’s just a real mess but it makes sense to the client and to the application that is consuming it.
A few days ago, whilst working on a client project and after dozens of tests, we started to see errors in our CSV file, where the headers and columns were arranged in a alphabetical sorting. This was not my intent, when I built the CSV array I wanted it to be in a certain order.
So why was this array now being sorted, who gave that command and how could I correct it?
Why and who:
As we dig in the Logic App code, we see that the Logic App is JSON in its core (my god, shocking development!). As such, it will follow JSON rules on sorting. If we set / append our variable with an array, even though that array won’t show up ordered in our code, it will when we deploy it to our Resource Group.
Lets prove this.
First, we set our LA in Visual Studio and initialize a string. Then we set two values to it ( “Append to string variable”) . One as a string and the other as an array.
Let’s look at the back code.
Looking good so far. Our strings are set and it’s in the order we want.
Lets deploy it to our RG and check again.
Well, there it is. In ARM deployment, if we write a JSON object, on deployment it gets sorted and will appear like this in the designer tool in Portal.
Funny thing is that if we change our object to the string we want, the designer will not recognize this as a change and doesn’t let you save.
Even in Code View the changes are not recognized.
But if we add other text to it, the changes are now recognized and Portal allows to save.
But still, it won’t show you the changes and will still sort out your CSV array, once again because it’s JSON.
A few weeks ago, this behavior wasn’t noticeable I had a few Logic Apps in place with the string array in a specific order and when deploying it didn’t get sorted.
I searched in Azure updates to see if anything was mentioned but nothing came up.
How to bypass this issue?
If you’re working with a CSV file like I was, after you build your array, you’ll need to build a CSV table.
The action “Create CSV table” will take care of this from you, but as we know, it will not be in the same format we need.
(notice I’ve switched to array variable because I can’t parse the string in JSON)
So, leaving the Columns in automatic mode will mess up your integration as you can see. The output will be sorted and it won’t be what you want / need.
What a mess!! This is nothing like we wanted.
We will need to manually define the columns headers and the value they’re going to have.
If you don’t have many fields, it’s quick to do this, but when you have lots of fields… well, let’s just say I hope you have plenty of time and don’t lose focus.
And there we have it. Fields are now displayed correctly, the data is in the right place and we’ve managed to get around this annoying problem.
Do you feel difficult to keep up to date on all the frequent updates and announcements in the Microsoft Integration platform and Azure iPaaS?
Integration weekly updates can be your solution. It’s a weekly update on the topics related to Integration – enterprise integration, robust & scalable messaging capabilities and Citizen Integration capabilities empowered by Microsoft platform to deliver value to the business.
Before we start with the actual post, I’d like to introduce a new brand we will be developing. This is the first official post with the “It’s not Rocket Science!” brand.
With this concept, we intend to explain how some procedures are not as complicated as you may think and that they’re not Rocket science. I’m open to suggestions on posts to demystify Azure Logic Apps and other Azure services.
So, welcome to a new concept and I hope you find it useful.
Azure Functions: Moving from Azure Portal to VS
Creating Functions in Portal presents some challenges, like lack of Intellisense, which, as we know, helps a lot. Not having CI/CD is also a concern and of the worse case scenarios:
Someone deleting you function by mistake!
Panic!, all hell broke loose. I tried to apply the same method as I did with the Logic Apps accidental delete recovery, but the “Change history” tab isn’t available for Functions. You end up losing your code, your executions and maybe some sleep over this.
The obvious next step is to get your backup from your repository and re-create the function. IF you have it in a repo.
We can’t prevent someone deleting our resources, everyone makes mistakes. But you can prevent letting your code sit in Azure Portal without version control, CI/CD and repository control.
So, the best way to do this is to migrate it to a VS solution. In this post, I will use VS2019, but VSCode is also available as others IDEs. You can do this right from the start by choosing another development environment besides the Portal.
With an existing Function, you’ll need a few things before you can migrate it. Besides your VS with an active subscription, you will also need the Azure SDK feature. This can be installed using the Installer you’ve downloaded from MS or in VS itself, in the “Tools”-> “Get Tools and Features” menu.
After a fresh install of Windows, I didn’t installed the Azure SDK, so I had to download it and install. According to the setup, it should take about 6,5GB.
If you had to install the SDK, Updates are also important, keep that in mind.
Lets return to the code.
The good thing about VS is that you don’t need to reference the required DLLs, they will probably already be there.
So you need to create a new Solution, with an Azure Function project.
Small note: you can also create a project using VB instead of C#, but… VB…
You can either choose a template with a trigger already created or you can choose an empty project, but when you try to Add a function, it will ask you again what template you want to use.
You can also add an empty class, but you will be missing some references.
As you can see, there are quite a few differences between the Class and the Function template.
I recommend you use the same template as the one you used to build your function, you will get more references that will be necessary for your code to work.
And now comes the easiest part. You can just copy-paste your code into VS, but leave the usings out. Most likely, if you haven’t used anything outstanding, they will already be there.
Now you have you code in VS, ready to run. Is it working properly? Well, you can just debug it locally, when pressing F5, the Azure emulator will start and you will be able to test your function like it was on the cloud.
You can just use your Postman or another web request tool to test your project.
Once you’ve tested everything and are ready to deploy to your subscription, you’ll need configure the publishing profile.
You have two major ways of doing this. You can choose the blue pill and configure your connection by hand or you take the red pill and download the profile from the existing FA.
My advice, get the publish profile. Saves you time and it’s Plug’n’Play.
And that’s it. Your newly migrated function is now ready for your CI/CD and you can manage and version it with VS and DevOps.
Now, this does have a catch, or not depending on how you look at it. Because we deploy using a Zip file, the code is no longer available in the Portal, you must now always use VS to view it.
I like this, because it means that from a Security perspective, noone will be accessing the source code through the Portal and it forces new Devs, and old ones too, to join the best practices policy and have everything in a proper Repo and version controlled.
Do you feel difficult to keep up to date on all the frequent updates and announcements in the Microsoft Integration platform and Azure iPaaS?
Integration weekly updates can be your solution. It’s a weekly update on the topics related to Integration – enterprise integration, robust & scalable messaging capabilities and Citizen Integration capabilities empowered by Microsoft platform to deliver value to the business.
A few days ago, I release the latest version of the BizTalk Scheduled Task Adapter optimized for BizTalk Server 2020. Today I merged the changes and improvements made on that version and made it available for BizTalk Server 2016.
What is new?
New schedule capabilities:
Capability to set DateTime you want to use on the Timespan schedule type: DateTime.Now or DateTime.UtcNow
By adding this capability to chose between DateTime.Now or DateTime.UtcNow, it will address and solve the issues reported regarding unlimited messages being triggered if you are using a different timezone than UTC.
The functionality always to trigger the message at 00 seconds and respect the original time set was added again to the Timespan schedule type. The GetNextActivationTime method was improved to fix all these unexpected behaviors reported that indeed was a feature by design.
BizTalk Scheduled Task Adapter
The BizTalk Scheduled Task Adapter is an in-process receive adapter that executes a prescribed task on a daily, weekly or monthly schedule. The adapter is configured entirely within BizTalk, all configurations are stored within the SSODB and can be exported and imported via binding files.
The schedule capabilities are similar to those available with the Windows Scheduled Task Service.
Four simple tasks are included:
XmlStringStreamProvider – generates a BizTalk message from a configured Xml string
FileStreamProvider – generates a BizTalk message from the contents of a file
HttpDownload – generates a BizTalk message from data downloaded from a web site
SQLStreamProvider – generates a BizTalk message from the contents of a SQL Query (similar to the old SQL adapter) – Since version 3.0
Custom tasks can be created. Any .NET class that implements the appropriate interface can be scheduled.
Big thanks to Pedro Almeida, my team member, that took 95% of my work and finalize this version!
Download
You can download this “new” version of the adapter in BizTalk Scheduled Task Adapter from GitHub:
Please feel free to use the adapter, and If you find any problems or bugs, please open an issue on the adapter GitHub site: https://github.com/sandroasp/BizTalk-Scheduled-Task-Adapter and help evolve this community adapter. You can also suggest new features, and I am also open to new ideas.
Do you feel difficult to keep up to date on all the frequent updates and announcements in the Microsoft Integration platform and Azure iPaaS?
Integration weekly updates can be your solution. It’s a weekly update on the topics related to Integration – enterprise integration, robust & scalable messaging capabilities and Citizen Integration capabilities empowered by Microsoft platform to deliver value to the business.
Recently while I was testing an integration solution on a client that uses WCF-SQL to insert data on a SQL Server database I got the following error:
System.NotSupportedException: The SqlDbType “” is not supported. Modify your table, view, stored procedure, or function definition to avoid having parameters or columns of this type.
This was a surprise since everything was working properly in the development environment.
Cause
In this solution, we were doing bulk insert on a SQL Server database by using a User-Defined Types as the input to the stored procedure, in our case a User-Defined Table Type. There are other ways to do a SQL Server bulk insert or update in BizTalk Server but definitely, this is the best approach. And this is one of the reasons why we are getting on the error message with the keyword: SqlDbType.
This problem occurs because the user account that you used to access the database, in my case the BizTalk Host Instance Account, don’t have permissions on the User-Defined Type associated to the Store procedure that we were invoking.
Solution
To solve this issue, you must give access to the user, in my case BizTalk Host Instance Account to properly execute the stored procedure, you must:
Open SQL Server Management Studio and connect to your server.
In the Object Explorer, select and expand the desired database and expand the Security folder and then the Users.
Right-click on the User, BizTalk Host Instance Account, and choose Properties.
On the Database User windows, choose the Owned Schemas tab, and then on the Schemas owned by this user panel select the schema bind to your User-Defined Table Type.
In our case: Material
Click OK and try again to send the data to your SQL Server. It should work.
