Please follow below steps to setup an SSIS package for uploading mulitple csv files to sFTP server.
STEP 1 - Setup Renci SSHNet DLL
For using sFTP in SSIS, we need a 3rd party Renci.SSHNet dll.
Dwnload the Renci.SSHNet from below or similar source. Please note we are not responsible whether such website is free from virus.
https://www.dllme.com/dll/files/renci_sshnet
The custom DLL needs to be added in the GAC
Open Developer Command Prompt from the Visual Studio 2019 Windows Start menu. Right-click and "Run As Administrator" to open the command prompt.
Try below,
gacutil -i "C:\SSIS\renci.sshnet.dll"
If failed, try below:
sn -Vr "C:\SSIS\renci.sshnet.dll"
gacutil -i "C:\SSIS\renci.sshnet.dll"
Check whether the Renci SSHNet DLL is added successfully in below folder.
C:\Windows\Microsoft.NET\assembly\GAC_MSIL
To unregister, run below command.
gacutil -u renci.sshnet
STEP 2 - Scripts
In Vision Studio, add a Script Task. Script Language is Microsoft Visual C#.
Click 'Edit Script...' button to edit the script.
Make sure below namespace are added.
#region Namespaces
using System;
using System.IO;
using Renci.SshNet;
Note, you can add the Renci SSHNet as reference. In the Script Editor, select Project - Add Reference... Click 'Browse' and select the required DLL.
Add/update below scripts. Please update the relevant path according to your environment.
public void Main()
{
string currentdatetime = DateTime.Now.ToString("yyyyMMddHHmmss");
string LogFolder = @"C:\SSIS\Log";
try
{
//Test upload all CSV files to FTP when running the package. Please call this sub-routine in the desired programming flow.
sFTPFileUpload();
}
//Register error in log
catch (Exception exception)
{
using (StreamWriter sw = File.CreateText(LogFolder + "\\" + "ErrorLog_" + currentdatetime + ".log"))
{
sw.WriteLine(exception.ToString() ) ;
}
}
Dts.TaskResult = (int)ScriptResults.Success;
}
//New sub routine for FTP upload.
public static void sFTPFileUpload()
{
var host = "ftp.XXXXX.com";
var port = 22; // sFTP uses port 22, FTP uses port 21
var username = "XXXXX";
var password = "YYYYYY";
string remotefolder = @"/Incoming/";
string loclfolder = @"E:\SSIS\";
string[] files = Directory.GetFiles(loclfolder, "*.csv");
foreach (string filePath in files)
{
var uploadFile = filePath;
using (var client = new SftpClient(host, port, username, password))
{
client.Connect();
client.ChangeDirectory(remotefolder);
if (client.IsConnected)
{
using (var fileStream = new FileStream(uploadFile, FileMode.Open))
{
client.BufferSize = 4 * 1024;
client.UploadFile(fileStream, Path.GetFileName(uploadFile));
}
}
else
{
}
}
}
}