Support for SFTP
Smart Integration Connector provides support for connecting to SFTP servers to send and retrieve files. Perform the steps in the following sections to establish a connection and then send and retrieve files.
IMPORTANT: It is best practice to utilize SSH.NET, which is included with Smart Integration Connector, for Secure File Transfer Protocol (SFTP) tasks.
IMPORTANT: As of version 9.1, WinSCP is no longer included with Smart Integration Connector. For current WinSCP users, it is a recommended best practice to transition your SFTP operations to the SSH.NET library. If you want to continue to use WinSCP, you will need to add WinSCP to your referenced assemblies folder and reference WinSCP from your remote business rules. See Support for DLL Migration.
NOTE: You must have an SFTP server available on a port. The port must be allowed for inbound and outbound connections on the Local Gateway Server. For this example, we have used port 22.
-
Log in to OneStream.
-
Navigate to System > Administration > Smart Integration Connector.
-
Create a New Connection and fill out all of the corresponding details for your Connection and the Gateway Server.
-
From Connection Type, select Direct Connection / Port Forwarding.
-
For Bound Port at Gateway, enter 22.
-
For Remote Gateway Host, enter the IP address or resolvable host name of the machine where your SFTP server is located.
-
For Bound Port in OneStream, select (Auto Assigned) (default and recommended setting) or Enter Port Manually. See Smart Integration Connector Terms for additional information.
-
(Auto Assigned) (default and recommended setting) to allow the OneStream application to automatically assign an unused port number. When the Direct Connection is created, the port number is shown in the connection settings.
-
Enter Port Manually: Enter an unused port number. The port number must be greater than 1024 and less than 65535.
-
-
Click OK.
-
Copy the Connection to the OneStream Smart Integration Connector Local Gateway Server Configuration.
-
Save the Local Gateway Server configuration and restart the Smart Integration Connector Gateway service.
Example: Here is an example of how you can upload and download files through an SFTP extensibility rule.
C# SFTP Example
Below you can find the C# example for STFP.
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Globalization;
using System.IO;
using System.Linq;
using Microsoft.CSharp;
using OneStream.Finance.Database;
using OneStream.Finance.Engine;
using OneStream.Shared.Common;
using OneStream.Shared.Database;
using OneStream.Shared.Engine;
using OneStream.Shared.Wcf;
using OneStream.Stage.Database;
using OneStream.Stage.Engine;
using Renci.SshNet;
namespace OneStream.BusinessRule.Extender.SFTP_SSH_C
{
public class MainClass
{
public object Main(SessionInfo si, BRGlobals globals, object api, ExtenderArgs args)
{
try
{
// --------------------------------------------------
// SSH.NET EXAMPLES
// --------------------------------------------------
// Setup SSH.NET session options from values in Cloud Administration Tools (CAT) Key Management - Secrets
var username = BRApi.Utilities.GetSecretValue(si, "SFTP-UserName");
var password = BRApi.Utilities.GetSecretValue(si, "SFTP-Password");
var authenticationMethod = new PasswordAuthenticationMethod(username, password);
var connectionInfo = new ConnectionInfo("localhost", username, authenticationMethod);
// Get the filepath - BatchHarvest in this example is File Share / Applications / GolfStreamDemo_v36 / Batch / Harvest
var fileDNpath = BRApi.Utilities.GetFileShareFolder(si, FileShareFolderTypes.BatchHarvest, null);
var fileSFTPpath = Path.Combine(fileDNpath, "SFTP_TEST_DOWNLOAD_" + DateTime.UtcNow.ToString("MM-dd-yyyy-HHmmss") + ".txt");
var fileSCPpath = Path.Combine(fileDNpath, "SCP_TEST_DOWNLOAD_" + DateTime.UtcNow.ToString("MM-dd-yyyy-HHmmss") + ".txt");
// SFTP Example
using (var sftpClient = new SftpClient(connectionInfo))
{
sftpClient.Connect();
using (var downloadStream = new FileStream(fileSFTPpath, FileMode.OpenOrCreate, FileAccess.Write, FileShare.None))
{
sftpClient.DownloadFile("SFTP_TEST_DOWNLOAD.txt", downloadStream);
}
}
// SCP Example
using (var scpClient = new ScpClient(connectionInfo))
{
scpClient.Connect();
scpClient.Download("SFTP_TEST_DOWNLOAD.txt", new FileInfo(fileSCPpath));
}
return null;
}
catch (Exception ex)
{
throw ErrorHandler.LogWrite(si, new XFException(si, ex));
}
}
}
}
VB STFP Example
Below you can find the VB example for STFP.
Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Data.Common
Imports System.Globalization
Imports System.IO
Imports System.Linq
Imports System.Windows.Forms
Imports Microsoft.VisualBasic
Imports OneStream.Finance.Database
Imports OneStream.Finance.Engine
Imports OneStream.Shared.Common
Imports OneStream.Shared.Database
Imports OneStream.Shared.Engine
Imports OneStream.Shared.Wcf
Imports OneStream.Stage.Database
Imports OneStream.Stage.Engine
Imports Renci.SshNet
Namespace OneStream.BusinessRule.Extender.SFTP_SSH
Public Class MainClass
Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Object, ByVal args As ExtenderArgs) As Object
Try
' --------------------------------------------------
' SSH.NET EXAMPLES
' --------------------------------------------------
' Setup SSH.NET session options from values in Cloud Administration Tools (CAT) Key Management - Secrets
Dim username As String = BRApi.Utilities.GetSecretValue(si, "SFTP-UserName")
Dim password As String = BRApi.Utilities.GetSecretValue(si, "SFTP-Password")
Dim authenticationMethod = New PasswordAuthenticationMethod(username, password)
Dim connectionInfo = New ConnectionInfo("localhost", username, authenticationMethod)
'Get the filepath - BatchHarvest in this example is File Share / Applications / GolfStreamDemo_v36 / Batch / Harvest
Dim fileDNPath As String = BRAPi.Utilities.GetFileShareFolder(si, FileShareFolderTypes.BatchHarvest, Nothing)
Dim fileSFTPpath = Path.Combine(fileDNpath, "SFTP_TEST_DOWNLOAD_" & DateTime.UtcNow.ToString("MM-dd-yyyy-HHmmss") & ".txt")
Dim fileSCPpath = Path.Combine(fileDNpath, "SCP_TEST_DOWNLOAD_" & DateTime.UtcNow.ToString("MM-dd-yyyy-HHmmss") & ".txt")
' SFTP Example
Using sftpClient = New SftpClient(connectionInfo)
sftpClient.Connect()
Using downloadStream = New FileStream(fileSFTPpath, FileMode.OpenOrCreate, FileAccess.Write, FileShare.None)
sftpClient.DownloadFile("SFTP_TEST_DOWNLOAD.txt", downloadStream)
End Using
End Using
' ' SCP Example
Using scpClient As New ScpClient(connectionInfo)
scpClient.Connect()
scpClient.Download("SFTP_TEST_DOWNLOAD.txt", New FileInfo(fileSCPpath))
End Using
Return Nothing
Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
Return Nothing
End Try
End Function
End Class
End Namespace
