SFTP Connection and File Manipulation in D365FO - arp6333/xplusplus GitHub Wiki
Users will be able to define and setup SFTP connection parameters, providing login and connection information from a form. Then files will be retrieved from SFTP and can be utilized for any use case.
User settings will be stored in a ConnectionParameters table. When the SFTP function is ran, a dialog will open allowing users to select the connection info needed for the chosen process and will allow running in batch.
This table will store any connection parameter information, allowing users to setup multiple connections. This can be customized per any use case needed with additional fields for any additional connection parameters desired.
-
ConnectionId, string: The Id of these parameters, the user will select this when choosing what parameters to use.
-
Description, string: Describes what these parameters are for.
-
FileTransferDirection, enum: Specifies inbound or outbound file transfer for these parameters.
-
Folder, string: The SFTP folder path for the files to be pulled from / placed in (ex. '/outbound/ExampleFolder').
-
SuccessNotification, string: Email address to send to upon successful file transfer (leave blank if no success emails desired).
-
FailureNotification, string: Email address to send to upon failed file transfer (leave blank if no failed emails desired).
-
SFTPPort, integer: Port to connect to SFTP with.
-
SFTPConnection, string: URL to connect to SFTP with.
-
SFTPPassword, string: Password to connect to SFTP with.
-
SFTPUserId, string: Username to connect to SFTP with.
-
FailedFolder, string: SFTP folder location to move files to upon failed file transfer (leave blank if no failed folder location desired).
-
ProcessedFolder, string: SFTP folder location to move files to upon successful file transfer (leave blank if no successful folder location desired).
Some more specifications on the table:
This will represent the selected connection parameter the user chooses. It will have a relation to the ConnectionId in the ConnectionParameters table.
Allows the user to select if this connection type is meant for inbound or outbound data.
The SFTPPassword field should extend the built in EncryptedField EDT. This will allow us to store the password in SQL encrypted. See here for more info on creating and using an encrypted field.
This enum won't be assigned to any of the fields in our table but will be used for processing. It can be passed in to tell a function whether to use the processed folder or the failed folder (see the moveFile function in the code section below for example).
The main form for connection parameters can look similar to this:
This allows the user to create a parameter id, description, and file transfer direction from the overview tab, and set the specifications in the details tab:
The form in Visual Studio looks like this:
We add the ConnectionInfo table as a datasource on the form:
Then create the overview tab page:
And the details tab page:
See Custom Dialog Class, Batch Job Class, & Remembering Parameters for more info on dialog / batch class creation.
This functionality utilizes .NET for creating and running the SFTP code.
Using:
using Microsoft.Azure;
using Microsoft.WindowsAzure.Storage;
using Microsoft.WindowsAzure.Storage.Blob;
using Microsoft.WindowsAzure.Storage.Blob.CloudBlobContainer;
using Microsoft.WindowsAzure.Storage.Blob.CloudBlockBlob;
using Microsoft.WindowsAzure.Storage.File;
using Renci.SshNet;
using Renci.SshNet.Common;
using Renci.SshNet.SftpClient;At the top of the class - Connection Parameter Dialog object, to allow the user to set the SFTP connection info; then the dialog method to create the connection parameter selector:
// Packed variables
int dummy;
// ConnectionParam for the user to select the needed connection
ConnectionParam connParam;
// Dialog field for ConnectionParam
DialogField dlgConnParam;
// ConnectionParameters table to retrieve the user selected params
ConnectionParameters connectionParameters;
#define.CurrentVersion(2)
#localmacro.CurrentList
dummy,
connParam
#endmacro
...
/// <summary>
/// Adds fields to the dialog box.
/// </summary>
/// <returns>The dialog box box.</returns>
public Object dialog()
{
DialogRunbase dialog;
//Setup the dialog
dialog = super();
dialog.caption("@Label:dataImportAuto");
// Create the selector for the SFTP connection parameters
dlgConnParam = dialog.addField(extendedTypeStr(ConnectionParam), "@Label:connParam");
if (connParam)
{
dlgConnParam.value(connParam);
}
return dialog;
}In the run method:
/// <summary>
/// Runs the SFTP process.
/// </summary>
public void run()
{
System.Collections.IEnumerable files;
System.Collections.IEnumerator fileEnumerator;
str fileName, emailSubject, emailBodyFileError;
Email userEmail;
Renci.SshNet.Sftp.SftpFile file;
fileDirectory = new List(Types::AnyType);
connectionParameters = ConnectionParameters::find(connParam);
userEmail = SysEmailParameters::find().SMTPUserName;
// Verify correct direction (inboud vs outbound)
if (connectionParameters.FileTransferDirection != FileTransferDirection::Inbound)
{
throw error ("@Label:inboundDirectionError");
}
// Create the connection info object
var connectionInfo = new ConnectionInfo(
connectionParameters.SFTPConnection,
connectionParameters.SFTPPort,
connectionParameters.SFTPUserId,
new PasswordAuthenticationMethod(
connectionParameters.SFTPUserId,
connectionParameters.SFTPPassword
)
);
// Get the file via SFTP
using (SftpClient sftp = new SftpClient(connectionInfo))
{
try
{
sftp.Connect();
// Check our folder exists on the SFTP server
if (!sftp.Exists(fileIntegrationParm.Folder))
{
throw error(strFmt("@Label:folderError", fileIntegrationParm.Folder));
}
files = sftp.ListDirectory(fileIntegrationParm.Folder, null);
fileEnumerator = files.GetEnumerator();
while (fileEnumerator.MoveNext())
{
try
{
file = fileEnumerator.Current;
fileName = strReplace(file.Name, '"', '');
//this.fileExtensionValidation(fileName); // Optional: validate the extension of the file is correct here
// Read file contents
using (var fileStream = sftp.OpenRead(fileName))
{
// Whatever you need to do with the file, do it here using the file stream
...
}
// Move file to processed location, or delete file (if needed by your use case)
if (connectionParameters.ProcessedFolder)
{
this.moveFile(sftp, FolderType::Processed, fileName);
}
else
{
sftp.DeleteFile(file);
}
}
catch
{
ex = CLRInterop::getLastException().GetBaseException();
// Move file to failed location (if needed by your use case)
if (connectionParameters.FailedFolder)
{
this.moveFile(sftp, FolderType::Failed, fileName);
}
// Send email notification alerting of failure (if needed by your use case)
if (userEmail)
{
emailSubject = strFmt("@Label:fileErrorSubject", fileName);
emailBodyFileError = strFmt("@Label:fileErrorBody", fileName, ex.Message);
// Send email here
}
Message::Add(MessageSeverity::Error, strFmt("@Label:fileError", fileName, ex.Message));
}
}
}
catch
{
ex = CLRInterop::getLastException().GetBaseException();
throw(ex);
}
finally
{
sftp.Disconnect();
}
}
}Helper methods:
/// <summary>
/// Moves a file to another folder via SFTP.
/// </summary>
/// <param name = "sftp">SFTP connection to use.</param>
/// <param name = "folderType">Which folder the file will be moved to.</param>
/// <param name = "fileName">Name of the file to move.</param>
public static void moveFiles(SftpClient sftp, FolderType folderType, str fileName)
{
str toFolder = connectionParameters.ProcessedFolder;
if (folderType == FolderType::Failed)
{
toFolder = connectionParameters.FailedFolder;
}
if (!sftp.Exists(toFolder))
{
throw error(strFmt("@Label:FolderDoesNotExist", toFolder));
}
str fromfile = connectionParameters.Folder + @"/" + fileName;
str tofile = toFolder + @"/" + fileName;
sftp.RenameFile(fromfile, tofile);
}
// Optional method to verify the file extension:
/// <summary>
/// Verify the file has the correct file extension
/// </summary>
/// <param name = "fullFileName">File name to verify.</param>
public void fileExtensionValidation(str fullFileName)
{
// Desired file extension:
str extension = 'xml';
// Actual file extension:
str actualFilePath, fileName, fileExtension;
[actualFilePath, fileName, fileExtension] = fileNameSplit(fullFileName);
// Compare file extensions
if (strLwr(fileExtension) != extension)
{
throw Error (strFmt("@Label:invalidFileExtension", fileExtension, extension));
}
}