I will show you how to connect to the OpenText GXS SFTP server from Azure. The Microsoft SFTP-SSH connector currently doesn’t support this SFTP server.
Logic Apps & the OpenText GXS SFTP server
If you try configure a SFTP-SSH trigger to connect to an OpenText GXS SFTP server you can browse to the top level folder.
Nevertheless, when you run it all you get is trigger failures. Furthermore, browsing to other SFTP folders fails. Thus, the SFTP server is not supported by Microsoft
A solution to connect to OpenText GXS from Azure
WINSCP to can connect SFTP server and get files from its folders. For this reason I choose to create an Azure function that uses WINSCP to do the job. Furthermore, you can use WINCP in an Azure function to transfer files. the solution is shown below.
The Azure function code
Firstly, reference the WINSCP .Net assembly.
Secondly, create a static blob client and retrieve its connection string.
Thirdly, create a recurrence trigger that runs every 5 minutes.
Fourthly, create a WINCP Session options object. Retrieve the username and SSH fingerprint from the function configuration settings. Retrieve the password from a key vault.
Fifthly, a list of files from the SFTP server matching the filename mask.
Sixthly, get each file from the SFTP server and save it to a blob container.
Finally, delete each file from the blob container.
using System;
using System.Collections.Generic;
using System.IO;
using System.Threading.Tasks;
using Microsoft.Azure.WebJobs;
using Microsoft.Azure.WebJobs.Host;
using Microsoft.Extensions.Logging;
using Microsoft.Azure.KeyVault;
using Microsoft.Azure.Services.AppAuthentication;
using Microsoft.WindowsAzure.Storage;
using Microsoft.WindowsAzure.Storage.Blob;
using WinSCP;
namespace BidOne.Edi.CustomerOrders.FunctionApps
{
public static class edi_sftp_poll_bp
{
private const string functionName = "edi_sftp_poll_bp";
//create static Blob Client
static string connectionString = Environment.GetEnvironmentVariable("AzureWebJobsStorage", EnvironmentVariableTarget.Process);
static CloudStorageAccount storageAccount = CloudStorageAccount.Parse(connectionString);
private static CloudBlobClient _blobClient = storageAccount.CreateCloudBlobClient();
[FunctionName("edi_sftp_poll_bp")]
public async static Task Run([TimerTrigger("0 */5 * * * *")]TimerInfo myTimer, ILogger log, ExecutionContext context)
{
log.LogInformation($"C# Timer trigger function executed at: {DateTime.Now}");
try
{
// Set up session options
SessionOptions sessionOptions = new SessionOptions
{
Protocol = Protocol.Sftp,
HostName = Environment.GetEnvironmentVariable("BPHostName", EnvironmentVariableTarget.Process),
UserName = "9429000048262",
Password = GetKeyVaultSecret("edi-kv-sftp-bp-NZ-User-Password", log, functionName),
SshHostKeyFingerprint = Environment.GetEnvironmentVariable("BPFingerPrint", EnvironmentVariableTarget.Process)
};
log.LogInformation("Starting");
// setup sessionOptions
using (Session session = new Session())
{
// Look for winscp.exe in the root folder of the function
session.ExecutablePath = Path.Combine(context.FunctionAppDirectory, "winscp.exe");
log.LogInformation("Connecting");
session.Open(sessionOptions);
IEnumerable<RemoteFileInfo> fileInfos =
session.EnumerateRemoteFiles("/",
"*_null_POLLABLE_*", WinSCP.EnumerationOptions.None);
foreach (RemoteFileInfo fileInfo in fileInfos)
{
log.LogInformation(fileInfo.Name);
//Set up retrieving from the blob container
CloudBlobContainer container;
CloudBlockBlob blob;
container = _blobClient.GetContainerReference("edi-customer-orders-blob");
await container.CreateIfNotExistsAsync();
blob = container.GetBlockBlobReference(String.Concat("BP","/",fileInfo.Name));
//Get the file from the SFTP Server and save it to blob
using (Stream stream = session.GetFile(fileInfo.Name))
{
await blob.UploadFromStreamAsync(stream);
}
//Delete file because GetFile does not have an option to remove
session.RemoveFile(fileInfo.Name);
}
}
log.LogInformation("Done");
}
catch (Exception e)
{
log.LogError(e, e.Message);
}
}
internal static string GetKeyVaultSecret(string secretName, ILogger log, string functionName)
{
var tokenProvider = new AzureServiceTokenProvider();
var vaultClient = new KeyVaultClient(new KeyVaultClient.AuthenticationCallback(tokenProvider.KeyVaultTokenCallback));
string kvUrl = Environment.GetEnvironmentVariable("AzureKeyVaultURL", EnvironmentVariableTarget.Process);
if (String.IsNullOrEmpty(kvUrl) == true)
{
string error = String.Format("{0}: The app setting AzureKeyVaultURL has not been set (to the KeyVault URL)", functionName);
log.LogInformation(error);
throw new Exception(error);
}
var secretUrl = string.Format("{0}/secrets/{1}", kvUrl, secretName);
string secret = Task.Run(async () => await vaultClient.GetSecretAsync(secretUrl)).Result.Value;
return secret;
}
}
}
Conclusion
In summary you can create an Azure function that gets a file from a OpenText GXS SFTP server. In addition this is an alternative to the Logic App SFTP-SSH trigger if you cannot use it.
