Export Business Central online entities to Azure storage blob container

As most probably know, it is not possible to access the file system while in Business Central cloud environment.

For example, in Dynamics NAV, we could have a job queue entry that, when run, creates a file and copies it in a network folder. We can still do that in an On-Premise environment, but not with cloud BC.

You could create the file and use DownloadFromStream, but that would only prompt you do download it locally, but would not copy it somewhere on a local or network folder.

If you try to use File.Create() you would get the warning: “The type or method ‘Create’ cannot be used for ‘Extension’ development”.

If your customer is happy to grab the file manually every time from the downloads folder then this should suffice:

But, if we want to automatize this process and run the extract on a regular basis, we need to find a cloud solution for storing the files.

Currently, there are 4 types of storage in Azure platform:

  • Containers/Blobs
  • File Shares
  • Queues
  • Tables

In my previous blog I dived into the Azure Storage of type Tables and tackled its API.

This blog is about interacting with the Azure storage blob containers:

  • manually, via Azure Portal
  • simulation, via VS Code extension “Rest Client”
  • Business Central extension
  • view blob container with Excel
  • get Azure Blobs locally

I found on Michael Megel’s blog a nice solution for exactly what I need. Awesome job on Blob Containers API, Michael! Thank you for sharing!

What I need:

  • Azure:
    • Set up a blob container to store Business Central exported files
    • Set up Storage Access Key
  • Simulation:
    • In VS Code, write requests with “Rest Client” extension, targeting Azure blob container API
  • Business Central:
    • A setup table in Business Central for Azure access stuff
    • Wrote an export interface that would allow users to run an action(“Write File in Azure”) that will send the extract to Azure container. The same code could be executed by a job queue.

Blob Container Setup

To set up a container, following Michael’s notes on above blog was enough for me.

For blob container accessibility I went on the path of shared access signature “SAS Token”.

Once created, you can start playing with the storage account container API.

I created the storage manually:

Drilling down into the storage account, I created a new container:

Simulation:

In VS Code, using Rest Client,

  1. I sent a request to get the list of containers:

Request:

GET https://svflorida.blob.core.windows.net/?comp=list&%5Bhere you insert your SAS token key]

content-type: application/json

Response:

HTTP/1.1 201 Created

Content-Length: 0

Content-MD5: 1B2M2Y8AsgTpgAmY7PhCfg==

Last-Modified: Wed, 18 Aug 2021 19:05:13 GMT

ETag: “0x8D9627B1BD88A0F”

Server: Windows-Azure-Blob/1.0 Microsoft-HTTPAPI/2.0

x-ms-request-id: 3f97555d-801e-006d-5263-94f646000000

x-ms-version: 2020-08-04

x-ms-content-crc64: AAAAAAAAAAA=

x-ms-request-server-encrypted: true

Date: Wed, 18 Aug 2021 19:05:13 GMT

Connection: close

2. I sent a PUT request to insert an empty file:

Request:

PUT https://svflorida.blob.core.windows.net/vendorlist/vl1111?%5Byour SAS token here]

x-ms-blob-type: BlockBlob

Content-Length: 0

Response:

HTTP/1.1 201 Created

Content-Length: 0

Content-MD5: 1B2M2Y8AsgTpgAmY7PhCfg==

Last-Modified: Wed, 18 Aug 2021 19:23:46 GMT

ETag: “0x8D9627DB340E9DD”

Server: Windows-Azure-Blob/1.0 Microsoft-HTTPAPI/2.0

x-ms-request-id: b77cbfb2-b01e-003b-2566-9407a9000000

x-ms-version: 2020-08-04

x-ms-content-crc64: AAAAAAAAAAA=

x-ms-request-server-encrypted: true Date: Wed, 18 Aug 2021 19:23:46 GMT

Connection: close

And this is the file in Azure portal:

Business Central extension:

This is how the new setup table “Azure Storage Setup” looks in BC:

This is how the new BC interface “Vendors Export Log” looks like:

“Write File In Azure” action on page 50251 “Vendor Export Log” does the following:

  • exports all BC vendors to a blob
  • the blob is then written to a PUT request content
  • the PUT request is sent to Azure Blob Storage API

Consult Blobs with Excel:

BC users can click on the URL link above and download locally the file or they, and other 3rd party users, can access the files via Excel, as I explained in my previous blog.

This time though, when creating the connection choose Data – > Get Data -> From Azure -> From Azure Blob Storage.

And finally displayed in the Excel book:

Get Azure Blobs locally

To help with getting the files locally, I wrote 2 blogs:

  • one about getting the files locally using Power Automate
  • one about Azure CLI to copy the files from azure blob storage locally

For more about storage accounts in Azure check this out.

You can find sample code repository here.

Copy files from Azure Blob Storage to File System (using Power Automate)

I found an older post on community.dynamics.com in which someone was asking for ways to automatically drop data extracts originated in BC SaaS into a local folder.

First, in SaaS, we can’t generate the files automatically and store them locally.

We need to store them in the cloud.

Once in the cloud, how can we automatically download them locally on a machine or a network folder?

I bing-ed the phrase “copy files from azure blob storage to file system” and the first search result was this link to a Power Automate template flow:

There are a multitude of cloud providers, but Microsoft does continuously a great job at connecting everything between BC SaaS, Azure platform, Power Automate and Power Apps, so it’s just convenient to use its tools.

To test it, I went through the following exercise:

  • In Azure Platform I created a storage account and in it I created a Blob Container.
    • “A container organizes a set of blobs, similar to a directory in a file system. A storage account can include an unlimited number of containers, and a container can store an unlimited number of blobs.”
  • I created a local folder that will be synchronized by the new flow with the container in Azure

In Power Automate, I started with the Template provided by Microsoft and set up the flow:

The flow requires two connectors to be set up:

  • one to the azure storage container
  • one to the local or network folder

Editing Azure Blob Storage we see that we need the name of the azure storage, in my case “svflorida” and storage access key:

Storage access key is located in in azure portal under Access Keys:

Editing the File System Connector:

The most time consuming, about half an hour, was to set up and troubleshooting the gateway.

The flow cannot just drop files from Azure on your machine. It needs a gateway.

To create a new gateway, click on the drop down and choose “+ New on-premises data gateway”.

That will prompt you to download an msi to install a gateway: GatewayInstall.msi.

Once gateway installed, the only change I’ve operated was to switch from HTTPS to TCP:

In a live environment I would investigate and maybe set up an Azure Service Bus, but for the purpose of this exercise I went with TCP.

Once that is done the flow will be triggered when new files are uploaded or deleted from the Azure Container.

I noticed that with my free trial license the recurrence of the flow was set to 3 minutes.

The flow seems to pick changes as expected, just be patient and wait for the next run 🙂

In azure portal, upload a new file into your container:

The file will appear after a few minutes in your local folder:

And the flow shows a successful run:

That’s it! In the next blog I will look into how I can generate BC SaaS extracts into an Azure storage container so the flow doesn’t feel useless 🙂

I hope this helps someone. In any way, it’s late here so I call it a night!