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 😉

13 Comments

  1. Hello Demiliani,

    First at all, thank you for this post, It’s really well explain and useful!
    I have a question about it, is it possible to use de UploadIntoStream function without showing the dialog window? Just passing by parameters our local folder and file, or something like that…

    Thank you, have a nice day

    Like

  2. Thank you Stefano for such a useful article, I have tried to implement the same functionality but got an Internal Server error, do you have any idea about this?

    Like

      1. Thank you, I have been able to resolve the issue.
        Moreover, I have faced another issue while uploading, when I uploaded the file to SFTP the file was uploaded successfully but it was empty. So for that, I have set the
        memoryStream.Position = 0;
        I am not sure anyone else faced this issue or not but writing this just in case can be helpful for other.

        Like

      2. Hi Demiliani, thank you for this useful article. Could you please share source code with all library and dll, it would be great help then. Thank you.

        Like

  3. Hello Demiliani! Greate article, but I havn’t found enywhere: how to import Renci.SshNet dependency in the script? I tried different ways, like the following:
    using Renci.SshNet;
    using Renci.SshNet.Common;
    using Renci.SshNet.Sftp;

    But Azure Functions doesn’t see the library and returns errors:
    2022-06-16T14:18:14.385 [Error] run.csx(54,29): error CS0246: The type or namespace name ‘SftpClient’ could not be found (are you missing a using directive or an assembly reference?)

    Like

  4. Thanks for reply! I thought that I’m able to write this code directly in the browser, without Visual Studio

    Like

  5. Thank you for the article. Is there a way we can directly upload the file to SFTP from BC once the file is generated? My client does not have blob storage, that is the reason I am asking for this, your help is much appreciated.

    Like

    1. Not directly from AL code. You can create an Azure Functions that takes the file stream (Base64) and then internally calls an SFTP for uploading the file directly. Same as the code in this article but without storing the file on Blob Storage.

      Like

Leave a Reply to selfservicebi Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.