This article will discuss how to upload and download files to an SFTP with SmartConnect 20.16 or newer using the WinSCP API.
This example is like the one where we put files onto an FTP site, however, users are needing to work with files from a more secure FTP Server. If you only need to connect to an FTP site, here is a help article for that scenario. SmartConnect uses .NET, either C# or VB.NET, for scripting to the .NET FTP library. By default there is not a Microsoft default .NET library for an SFTP site. When connecting to a SFTP site, an application that specializes in that connection type is required.
For this article, I will be using WinSCP as my connecting application and library. I found it quite easy to use for connecting to my SFTP site, which is just a test site called Wing FTP. One nice feature of using an application such as WinSCP is the possibility of using different protocols instead of just SFTP, so I could technically use this same code library for my standard FTP site as well.
WinSCP Install
On the WinSCP install, check the Add installation directory to search path checkbox. T
This will ensure that the .NET compiler can always find the WinSCPnet.dll on script compilation.
- Make sure you can connect to your site using this application before trying to set up your task inside of SmartConnect.
- Try uploading and downloading files as well to ensure the user and password we will use in our script has the necessary authorization to complete the task.
SmartConnect
Secure Global Variables
In the SmartConnect Script Task we don’t want to enter the User Name and Password in plain text, so we want to create some secure global variables to enter that information to encrypt it from users who should not have that information. The other advantage to using Global Variables is changing the value once will update any maps using that value without having to go to each map and update.
- Log into SmartConnect
- Navigate to Maintenance > Secure Global Variables
- Create the following variables
Script Namespace
To be able to use the WinSCP library within SmartConnect, we need to add the DLL to the SmartConnect Namespace table and copy the DLL to a few locations as well.
- C:\Program Files\eOne Solutions\SmartConnect (include any location where SmartConnect has been installed)
- C:\Program Files\eOne Solutions\SmartConnect API\bin (wherever the SmartConnect WCF Web Service is installed)
- C:\Windows\SysWOW64 (on machine where the SmartConnect Windows Service has been installed)
- C:\Windows\Microsoft.NET\Framework64\v4.0.30319
Script Namespace
- Open SmartConnect
- Navigate to File > Maintenance > Script Namespaces
- In the Assembly Name field enter WinSCPNet
- Tab out of the field
- Click on the WinSCP namespace in the Namespace(s) list
- Click Add Selected
- Click OK
- Close and reopen SmartConnect
- C:\Program Files (x86)\eOne Solutions\SmartConnect (include any location where SmartConnect has been installed)
- C:\Program Files (x86)\eOne Solutions\wcf\bin (wherever the SmartConnect WCF Web Service is installed)
- C:\Program Files (x86)\eOne Solutions\www\bin (wherever the SmartConnect Web Service is installed)
- C:\Windows\SysWOW64 (on machine where the SmartConnect Windows Service has been installed)
- C:\Windows\Microsoft.NET\Framework\v4.0.30319
Script Namespace
- Open SmartConnect
- Navigate to Maintenance > Generic Connector > Script Name Spaces
- In the Assembly Name field enter WinSCPNet
- Tab out of the field
- Click on the WinSCP namespace in the Namespace(s) list
- Click Add Selected
- Click OK
- Close and reopen SmartConnect
Destination
We are simply going to export the source data to a CSV file and use this file in our Task below.
Tasks
If you want to or need to take advantage of other features of the WinSCP API that are not specified in this example, you can view their documentation here.
Upload
In a typical upload situation, we need to upload the file after the map is completed, but it could also be written as a task after each document or on any task within the SmartConnect map.
With our Tasks that run if the map succeeds, we will right-click and choose New Task > Run Script.
Dim fileName as string
Dim uploadFolder as string
filename = "C:UserseOneDocumentsTestSFTPuploads*" ' files to be loaded from this folder. wilcards can be used
uploadFolder = "/upload/" ' Default folder where files will be loaded
Try
Dim sessionOptions as new WinSCP.SessionOptions
with sessionOptions
.Protocol = WinSCP.Protocol.Sftp
.HostName = GBL_SFTP_HOST_NAME
.UserName = GBL_SFTP_USERNAME
.Password = GBL_SFTP_PASSWORD
.PortNumber = 2222
.SshHostKeyFingerprint = GBL_SFTP_SSH_KEY
end with
using session as new WinSCP.Session
session.Open(sessionOptions)
Dim transferOptions as new WinSCP.TransferOptions
transferOptions.TransferMode = WinSCP.TransferMode.Binary
Dim transferResult as WinSCP.TransferOperationResult
transferResult = session.PutFiles(Filename, uploadFolder, false, transferOptions)
transferResult.Check() 'THrows the first error if not successful
For Each transfer as WinSCP.TransferEventArgs in transferResult.Transfers
'Write results to Event Viewer or SQL table or Log Table
'This will display a message box with the results
MessageBox.Show("Upload of file " & transfer.FileName & " successful.")
Next
end using
Catch ex as System.Exception
MessageBox.Show(ex.ToString()) 'Display any Exceptions
Finally
End Try
return true
string fileName = null;
string uploadFolder = null;
fileName = "C:\Users\eOne\Documents\TestSFTP\uploads\*";
// files to be loaded from this folder. wilcards can be used
uploadFolder = "/upload/";
// Default folder where files will be loaded
try
{
WinSCP.SessionOptions sessionOptions = new WinSCP.SessionOptions();
var _with1 = sessionOptions;
_with1.Protocol = WinSCP.Protocol.Sftp;
_with1.HostName = GBL_SFTP_HOST_NAME;
_with1.UserName = GBL_SFTP_USERNAME;
_with1.Password = GBL_SFTP_PASSWORD;
_with1.PortNumber = 2222;
_with1.SshHostKeyFingerprint = GBL_SFTP_SSH_KEY;
using (WinSCP.Session session = new WinSCP.Session[])
{
session.Open(sessionOptions);
WinSCP.TransferOptions transferOptions = new WinSCP.TransferOptions();
transferOptions.TransferMode = WinSCP.TransferMode.Binary;
WinSCP.TransferOperationResult transferResult = default(WinSCP.TransferOperationResult);
transferResult = session.PutFiles(fileName, uploadFolder, false, transferOptions);
transferResult.Check();
//THrows the first error if not successful
foreach (WinSCP.TransferEventArgs transfer in transferResult.Transfers)
{
//Write results to Event Viewer or SQL table or Log Table
//This will display a message box with the results
MessageBox.Show("Upload of file " + transfer.FileName + " successful.");
}
}
}
catch (System.Exception ex)
{
MessageBox.Show(ex.ToString());
//Display any Exceptions
}
finally
{}
return true;
Download
In the download scenario, we want to pull file(s) from the SFTP site and put them into a folder data source using a pre integration task.
Set the Folder Data Source properties where the files will need to be placed.
Create the Get Files Run Script Task on the Tasks that run before the map to place them into the Source folder defined in the data source section of the map.
Dim downloadFolder as string
Dim ftpFolder as string
downloadFolder = "C:\Users\eOne\Documents\TestSFTP\source\" ' files to be loaded from this folder. wilcards can be used
ftpFolder = "/upload/" ' Default folder where files will be loaded
Try
Dim sessionOptions as new WinSCP.SessionOptions
with sessionOptions
.Protocol = WinSCP.Protocol.Sftp
.HostName = GBL_SFTP_HOST_NAME
.UserName = GBL_SFTP_USERNAME
.Password = GBL_SFTP_PASSWORD
.PortNumber = 2222
.SshHostKeyFingerprint = GBL_SFTP_SSH_KEY
end with
using session as new WinSCP.Session
session.Open(sessionOptions)
Dim transferOptions as new WinSCP.TransferOptions
transferOptions.TransferMode = WinSCP.TransferMode.Binary
Dim transferResult as WinSCP.TransferOperationResult
transferResult = session.GetFiles(ftpFolder, downloadFolder, false, transferOptions)
transferResult.Check() 'Throws the first error if not successful
For Each transfer as WinSCP.TransferEventArgs in transferResult.Transfers
'Write results to Event Viewer or SQL table or Log Table
'This will display a message box with the results
MessageBox.Show("Download of file " & transfer.FileName & " successful.")
Next
end using
Catch ex as System.Exception
MessageBox.Show(ex.ToString()) 'Display any Exceptions
Finally
End Try
return true
string downloadFolder = null;
string ftpFolder = null;
downloadFolder = "C:\\Users\\eOne\\Documents\\TestSFTP\\source\\"; // files to be loaded from this folder. wilcards can be used
ftpFolder = "/download/"; // Default folder where files will be retrieved from
try
{
WinSCP.SessionOptions sessionOptions = new WinSCP.SessionOptions();
var _with1 = sessionOptions;
_with1.Protocol = WinSCP.Protocol.Sftp;
_with1.HostName = GBL_SFTP_HOST_NAME;
_with1.UserName = GBL_SFTP_USERNAME;
_with1.Password = GBL_SFTP_PASSWORD;
_with1.PortNumber = 2222;
_with1.SshHostKeyFingerprint = GBL_SFTP_SSH_KEY;
using (WinSCP.Session session = new WinSCP.Session[])
{
session.Open(sessionOptions);
WinSCP.TransferOptions transferOptions = new WinSCP.TransferOptions();
transferOptions.TransferMode = WinSCP.TransferMode.Binary;
WinSCP.TransferOperationResult transferResult = default(WinSCP.TransferOperationResult);
transferResult = session.GetFiles(ftpFolder, downloadFolder, false, transferOptions);
transferResult.Check(); //Throws the first error if not successful
foreach (WinSCP.TransferEventArgs transfer in transferResult.Transfers)
{
//Write results to Event Viewer or SQL table or Log Table
//This will display a message box with the results
MessageBox.Show("Download of file " + transfer.FileName + " successful.");
}
}
}
catch (System.Exception ex)
{
MessageBox.Show(ex.ToString());
//Display any Exceptions
}
finally
{
}
return true;
Conclusion
This is just one example of an application that can be used with SmartConnect for sending and retrieving files from an SFTP site.
