Dynamics 365 Business Central SaaS: save a file to an SFTP server

In our recently released “Mastering Dynamics 365 Business Central” book, in the Azure Function chapter I’ve provided a full example on how to upload and download a file to Azure Blob Storage from a SaaS environment (this was one of the top request I’ve received on all my trainings this year). But many of you have also raised a new more request: in a Dynamics 365 Business Central SaaS environment, how can I save a file to an SFTP server?

This is an operation that you cannot do directly from a SaaS tenant, simply because from here you don’t have access to local resources and you cannot execute custom code. In this blog post I want to give you a possible solution that involves using a C# Azure Functions.

The Azure Function that we’ll use for this task is an HttpTrigger with a function called UploadFile defined as follows:

        [FunctionName("UploadFile")]
        public static async Task<IActionResult> Upload(
            [HttpTrigger(AuthorizationLevel.Function, "post", Route = null)] HttpRequest req,
            ILogger log)
        {
            log.LogInformation("C# HTTP trigger function processed a request.");

            string requestBody = await new StreamReader(req.Body).ReadToEndAsync();
            dynamic data = JsonConvert.DeserializeObject(requestBody);

            string base64String = data.base64;
            string fileName = data.fileName;
            string fileType = data.fileType;
            string fileExt = data.fileExt;
            Uri uri = await UploadBlobAsync(base64String, fileName, fileType, fileExt);
            //Upload to SFTP
            fileName = await UploadFileToSFTP(uri, fileName);

            return fileName != null
                ? (ActionResult)new OkObjectResult($"File {fileName} stored. URI = {uri}")
                : new BadRequestObjectResult("Error on input parameter (object)");
        }

The skelethon of this function is very similar to the sample provided in my book. The function receives a POST request with a JSON object that contains the file data (Base64) to upload and then other parameters like the name of the file, the file type and the file extension.

Then, this function uploads the file to an Azure BLOB Storage container (here called d365bcfiles, but this could be a parameter) by calling the UploadBlobAsync method. This method is defined as follows:

        public static async Task<Uri> UploadBlobAsync(string base64String, string fileName, string fileType, string fileExtension)
        {
            string contentType = fileType;
            byte[] fileBytes = Convert.FromBase64String(base64String);

            CloudStorageAccount storageAccount = CloudStorageAccount.Parse(BLOBStorageConnectionString);
            CloudBlobClient client = storageAccount.CreateCloudBlobClient();
            CloudBlobContainer container = client.GetContainerReference("d365bcfiles");

            await container.CreateIfNotExistsAsync(
              BlobContainerPublicAccessType.Blob,
              new BlobRequestOptions(),
              new OperationContext());
            CloudBlockBlob blob = container.GetBlockBlobReference(fileName);
            blob.Properties.ContentType = contentType;

            using (Stream stream = new MemoryStream(fileBytes, 0, fileBytes.Length))
            {
                await blob.UploadFromStreamAsync(stream).ConfigureAwait(false);
            }

            return blob.Uri;
        }

Then, when the file is uploaded to the Azure BLOB Storage, the function calls the UploadFileToSFTP method that is responsible to take this file and upload it to an SFTP server. This method is defined as follows:

        private static async Task<string> UploadFileToSFTP(Uri uri, string sourceFileName)
        {                        
            string storageAccountContainer = "d365bcfiles";            
            string storageConnectionString = BLOBStorageConnectionString;            
            string sourceFileAbsolutePath = uri.ToString();
            //SFTP Parameters (read it from configurations or Azure KeyVault)
            string sftpAddress = "YOUR FTP ADDRESS";
            string sftpPort = "YOUR FTP PORT";
            string sftpUsername = "YOUR FTP USERNAME";
            string sftpPassword = "YOUR FTP PASSWORD";
            string sftpPath = "YOUR FTP PATH";
            string targetFileName = sourceFileName;
            var memoryStream = new MemoryStream();

            CloudStorageAccount storageAccount;
            if (CloudStorageAccount.TryParse(storageConnectionString, out storageAccount))
            {
                CloudBlobClient cloudBlobClient = storageAccount.CreateCloudBlobClient();
                CloudBlobContainer cloudBlobContainer = cloudBlobClient.GetContainerReference(storageAccountContainer);
                
                CloudBlockBlob cloudBlockBlobToTransfer = cloudBlobContainer.GetBlockBlobReference(new CloudBlockBlob(uri).Name);
                await cloudBlockBlobToTransfer.DownloadToStreamAsync(memoryStream);
            }

            var methods = new List<AuthenticationMethod>();
            methods.Add(new PasswordAuthenticationMethod(sftpUsername, sftpPassword));

            //Connects to the SFTP Server and uploads the file 
            Renci.SshNet.ConnectionInfo con = new Renci.SshNet.ConnectionInfo(sftpAddress, sftpPort, new PasswordAuthenticationMethod(sftpUsername, sftpPassword));
            using (var client = new SftpClient(con))
            {
                client.Connect();
                client.UploadFile(memoryStream, $"/{sftpPath}/{targetFileName}");                
                client.Disconnect();
                return targetFileName;
            }

        }

For connecting to the SFTP server here I’m using a free library (available as a NuGet package directly from Visual Studio) that is called SSH.NET.

Calling this function from an AL extension is quite simple and it’s exacly the same sample that you can find in my book. In this sample, I have a codeunit with a method called UploadFile that permits you to select a local file and upload it. Obviously, you can avoid the “local upload” piece of code and use the same method to pass a dynamically generated file (a report and so on). The AL code is as follows:

    procedure UploadFile()
    var
        fileMgt: Codeunit "File Management";
        httpClient: HttpClient;
        httpContent: HttpContent;
        jsonBody: text;
        httpResponse: HttpResponseMessage;
        httpHeader: HttpHeaders;
        fileName: Text;
        fileExt: Text;
        InStr: InStream;
        base64Convert: Codeunit "Base64 Convert";
    begin
        UploadIntoStream('Select a file to upload', '', 'All files (*.*)|*.*', fileName, InStr);
        fileExt := fileMgt.GetExtension(fileName);

        jsonBody := ' {"base64":"' + base64Convert.ToBase64(InStr) +
        '","fileName":"' + fileName + '.' + fileExt +
        '","fileType":"' + GetMimeType(fileName) + '", "fileExt":"' + fileMgt.GetExtension(fileName) + 
            '"}';

        httpContent.WriteFrom(jsonBody);
        httpContent.GetHeaders(httpHeader);
        httpHeader.Remove('Content-Type');
        httpHeader.Add('Content-Type', 'application/json');
        httpClient.Post(BaseUrlUploadFunction, httpContent, httpResponse);
        //Here we should read the response to retrieve the URI
        message('File uploaded.');
    end;

Some notes about this implementation:

  1. the file is not removed from the Azure Blob Storage container (because I want this behaviour). If you want, you can remove it after the SFTP upload.
  2. If the file is saved into the Blob Storage but the SFTP upload fails for some reasons, the file is not uploaded again (there’s no a retry logic). You need to restart the UploadFile action. For this reason, you could consider to create a TimerTrigger Azure Function that every N unit time checks the Azure Blob Storage for files and then (if not empty) upload them to SFTP by calling the UploadFileToSFTP method.
  3. Use Azure Key Vault to store all your credentials.

Hope it helps many of you… happy coding

Comment List
Related
Recommended