Use SSIS with WinSCP to Download Files Via SFTP
Creating a script tasks to handle SFTP processing, using WinSCP, can be intimidating. However, once you understand how the pieces fit together, the coding is quite simple. In this post, I will show you how to create an SSIS script task for downloading a file, via SFTP. We will be setting up our environment before creating the SSIS project. We will be completing four different tasks in this tutorial.
1. Creating directories that will be used by our SSIS script task.
2. Downloading and installing WinSCP to obtain the necessary files.
3. Download a local SFTP server for developing and testing, not needed if you already have a Development environment and credentials.
4. Create the SSIS project and script task that will implement the WinSCP.net assembly.
Task 1 - Directory Creation
Create the following directories.
C:\csharp_lib\WinSCP.Net - The name is arbitrary, but you should use the provided directory name and structure for this tutorial. Feel free to change the naming structure to your preference once you have a working proof of concept.
Note: The directory structure will need to exist, in the same location, in all environments where you plan to deploy the project (i.e. locally, Development server, QA Server, Production server. The directory structure and name must be consistent across environments.)
WinSCP .exe and dll versions should be kept consistent across devices. This will make sure that any errors are not directly related to version issues.
C:\Import - The name is arbitrary, but required for use with this tutorial. This directory will serve as a staging area for the files imported using SFTP.
C:\Export - The name is arbitrary, but required for use with the next tutorial, demonstrating how to upload a file. This directory will serve as a staging area for the files we will export using SFTP.
C:\LogFiles-The name is arbitrary, but required for use with this tutorial. This directory will store the log files generated by our SSIS script task
Task 2 - WinSCP Files
The WinSCP executable and DLL are obtained by downloading and installing WinSCP. You can download the installation file by going to https://winscp.net/
Here is a brief tutorial, if needed, https://www.youtube.com/watch?v=58KmUBaEW34.
Once installation is complete, navigate to your C:\Program Files (x86)\WinSCP directory and copy the following files:
• WinSCP.exe
• WinSCPnet.dll
Navigate to C:\csharp_lib\WinSCP.Net and paste the files you just copied.
Task 3 - Development SFTP Server
If you do not have access to a Development SFTP server, you can obtain a local SFTP server for testing purposes, Rebex Tiny SFTP Server: https://www.rebex.net/tiny-sftp-server/
Simply download and unpack the zip file to a directory of your choice. Free for commercial and non-commercial use. It is not recommended for Production level use.
Double click on the Rebex Tiny Sftp Server application file. Click on the start button in the application window to start the local SFTP server.
You now have a local development SFTP server.
Task 4 - SSIS Package Development
Create the following package level variables.
Package Level Variables
Name Scope Data Type Value
SFTP_FileName Package String testfile.txt
SFTP_FingerPrint Package String See below
SFTP_FullFileImportPath Package String See below
SFTP_ImportPath Package String /
SFTP_LogFileName Package String See below
SFTP_Password Package String password
SFTP_PortNumber Package Int 22
SFTP_Server Package String See below
SFTP_User Package String tester
vFileExistsFlag Package Int 0
vLocalFilePath Package String c:\Import\
1. SFTP_FileName – Name of the file we will be importing from the SFTP server.
2. SFTP_FingerPrint – You can obtain the SshHostKeyFingerprint value by using WinSCP to generate a code template providing you with the key. To learn how.
3. SFTP_FullFileImportPath – Combination of two package variables. @[User::SFTP_ImportPath]+ @[User::SFTP_FileName]
4. SFTP_ImportPath – Path to file on remote server.
5. SFTP_LogFileName – Dynamically created file name using an expression. There are many date expressoin examples for SSIS on the web. This link can provide some detail on the expression. The value will be c:\LogFiles\SFTP_Import_MMDDYY_log.txt, which is generated by using the following expression ( copy all of the text including double quotes):
"c:\\LogFiles\\SFTP_Import_"+(LEN((DT_STR,2,1252)(DATEPART("MM",GETDATE()))) == 2 ? (DT_STR,2,1252)(DATEPART("MM",GETDATE())) : "0" + (DT_STR,2,1252)(DATEPART("MM",GETDATE()))) +(LEN((DT_STR,2,1252)(DATEPART("dd",GETDATE()))) == 2 ? (DT_STR,2,1252)(DATEPART("dd",GETDATE())) : "0" + (DT_STR,2,1252)(DATEPART("dd",GETDATE()))) +(DT_STR,4,1252)(DATEPART("yyyy",GETDATE())%100)+"_log.txt"
6. SFTP_Password – “password”, the password is fixed and defined by Rebex Tiny SFTP server change if you are using your own development server.
7. SFTP_PortNumber– 22, standard for SFTP connections.
8. SFTP_Server – Defined by Rebex Tiny SFTP Server when the server is started. Your value will differ from mine.
9. SFTP_User – “Tester”, this user name is fixed and defined by Rebex Tiny SFTP server.
10. vFileExistsFlag – 0, value will be updated by SSIS script task, at runtime, if the file exists.
11. vLocalFilePath - c:\Import\, this is the directory that will serve as a staging area for the imported file.
SFTP credentials are displayed when Rebex Tiny SFTP Server is started.
Package Development
Step 1
Create an SSIS project and package.
Step 2
Drag a script task onto the canvass.
Step 3
Double click on the script task and ensure C# is the selected language.
Step 4
Mark the following variables as ReadOnly Variables.
• User::SFTP_FingerPrint
• User::SFTP_FullFileImportPath
• User::SFTP_LogFileName
• User::SFTP_Password
• User::SFTP_PortNumber
• User::SFTP_Server
• User::SFTP_User
• User::vLocalFilePath
Your script task editor window should resemble the following image.
Step 5
Mark the following variables as ReadWrite variables.
• User::vFileExistsFlag
Your script task editor window should resemble the following image.
Step 6
Click on the “Edit Script” button to open up the script task editor. We will need to add a reference to the WinSCP.net Assembly in the next step. We will also add WinSCP to the list of namespaces.
Step 7
Add a reference to the WinSCP.net assembly.
• Right click on References and select the “Add Reference” menu item.
Click on the browse button and navigate to the directory the WinSCP.net assembly is stored.
Select the WinSCP dll file, and click the “Add” button.
Click on “Ok” on the next window.
Add WinSCP to the namespace. Remove the namespaces for System.Data and System.Windows.Forms.
Step 8 – Enter the script to call the WinSCP.net assembly.
Add the following script to create and entry point for calling the WinSCP.net assembly from a location outside of the Global Assembly Cache (GAC). Placement is just above public void Main(). The source of this code and more detailed information can be found here Assembly in SSIS outside of the GAC.
static ScriptMain()
{
AppDomain.CurrentDomain.AssemblyResolve += new ResolveEventHandler(CurrentDomain_AssemblyResolve);
}
static System.Reflection.Assembly CurrentDomain_AssemblyResolve(object sender, ResolveEventArgs args)
{
if (args.Name.Contains("WinSCPnet"))
{
string path = @"C:\csharp_lib\WinSCP.Net\";
return System.Reflection.Assembly.LoadFile(System.IO.Path.Combine(path, "WinSCPnet.dll"));
}
return null;
}
Step 9 – Populate values for SessionOptions class properties.
SessionOptions sessionOptions = new SessionOptions
{
Protocol = Protocol.Sftp,
HostName = Dts.Variables["User::SFTP_Server"].Value.ToString(),
PortNumber = (int)Dts.Variables["User::SFTP_PortNumber"].Value, //Requires an integer.
UserName = Dts.Variables["User::SFTP_User"].Value.ToString(),
Password = Dts.Variables["User::SFTP_Password"].Value.ToString(),
SshHostKeyFingerprint = Dts.Variables["User::SFTP_FingerPrint"].Value.ToString(),
};
Step 10 - Encapsulate the remaining code in a Try...Catch statement
try
{
using (Session session = new Session())
{
//Set absolute path for log file creation.
session.SessionLogPath = Dts.Variables["User::SFTP_LogFileName"].Value.ToString();
//Automatic connection and authentication of the session.
session.Open(sessionOptions);
//Absolute path to the file on the SFTP server.
string curFile = Dts.Variables["User::SFTP_FullFileImportPath"].Value.ToString();
if (session.FileExists(curFile))//FileExists?
{
//Default constructor.
TransferOptions transferOptions = new TransferOptions();
//Use binary mode to transfer all files unless you know the file contains only
// text, then you are able to use ASCII mode.
transferOptions.TransferMode = TransferMode.Binary;
//With SFTP protocol, WinSCP by default transfers files over 100 KB via a
//temporary file. It will fail if you do not have permissions to create a new
//file. Turning the option OFF avoids possible failure.
transferOptions.ResumeSupport.State = TransferResumeSupportState.Off;
//Get files from server
TransferOperationResult TransferResult = session.GetFiles(Dts.Variables
["User::SFTP_FullFileImportPath"].Value.ToString(),
Dts.Variables["User::vLocalFilePath"].Value.ToString(), false, transferOptions);
// Checks if there is any failure
TransferResult.Check();
//Set File Exists variable value when file is found.
Dts.Variables["User::vFileExistsFlag"].Value = 1;
// Print results, displayed on Execution Results tab.
bool fireAgain = false;
foreach (TransferEventArgs transfer in TransferResult.Transfers)
{
Dts.Events.FireInformation(0, null,
string.Format("Download of {0} succeeded", transfer.FileName),
null, 0, ref fireAgain);
}
}
else
{
Dts.Variables["User::vFileExistsFlag"].Value = 0;//Value if file is not found.
}
}
Dts.TaskResult = (int)ScriptResults.Success;
}
//Print Errors, displayed on Execution Results tab.
catch (Exception e)
{
Dts.Events.FireError(0, null,
string.Format("Error when using WinSCP to download files: {0}", e),
null, 0);
Dts.TaskResult = (int)DTSExecResult.Failure;
}
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
Step 11 - Testing
Start Tiny SFTP server by double clicking on the Rebex Tiny SFTP Server executable file in the directory containing the application contents.
Click on the start button in the application window.
You should see a window resembling the following. Your server IP will differ.
Once the SFTP server is started, execute the package. You should now have testfile.txt in the C:\Import directory.
Navigate to C:\LogFiles. You should see a log file related to your WinSCP session.
Code in its Entirety
#region Namespaces
using System;
using Microsoft.SqlServer.Dts.Runtime;
using WinSCP;
#endregion
namespace ST_baea964635034bbf9735e63b0f979109
{
[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
static ScriptMain()
{
AppDomain.CurrentDomain.AssemblyResolve += new ResolveEventHandler(CurrentDomain_AssemblyResolve);
}
static System.Reflection.Assembly CurrentDomain_AssemblyResolve(object sender, ResolveEventArgs args)
{
if (args.Name.Contains("WinSCPnet"))
{
string path = @"C:\csharp_lib\WinSCP.Net\";
return System.Reflection.Assembly.LoadFile(System.IO.Path.Combine(path, "WinSCPnet.dll"));
}
return null;
}
public void Main()
{
SessionOptions sessionOptions = new SessionOptions
{
Protocol = Protocol.Sftp,
HostName = Dts.Variables["User::SFTP_Server"].Value.ToString(),
PortNumber = (int)Dts.Variables["User::SFTP_PortNumber"].Value,
UserName = Dts.Variables["User::SFTP_User"].Value.ToString(),
Password = Dts.Variables["User::SFTP_Password"].Value.ToString(),
SshHostKeyFingerprint = Dts.Variables["User::SFTP_FingerPrint"].Value.ToString(),
};
try
{
using (Session session = new Session())
{
session.SessionLogPath = Dts.Variables["User::SFTP_LogFileName"].Value.ToString();
session.Open(sessionOptions);
string curFile = Dts.Variables["User::SFTP_FullFileImportPath"].Value.ToString();
if (session.FileExists(curFile))
{
TransferOptions transferOptions = new TransferOptions();
transferOptions.TransferMode = TransferMode.Binary;
transferOptions.ResumeSupport.State = TransferResumeSupportState.Off;
TransferOperationResult TransferResult = session.GetFiles(Dts.Variables["User::SFTP_FullFileImportPath"].Value.ToString(), Dts.Variables["User::vLocalFilePath"].Value.ToString(), false, transferOptions);
TransferResult.Check();
Dts.Variables["User::vFileExistsFlag"].Value = 1;
bool fireAgain = false;
foreach (TransferEventArgs transfer in TransferResult.Transfers)
{
Dts.Events.FireInformation(0, null,
string.Format("Download of {0} succeeded", transfer.FileName),
null, 0, ref fireAgain);
} }
else
{
Dts.Variables["User::vFileExistsFlag"].Value = 0;
}
}
Dts.TaskResult = (int)ScriptResults.Success;
}
catch (Exception e)
{
Dts.Events.FireError(0, null,
string.Format("Error when using WinSCP to download files: {0}", e),
null, 0);
Dts.TaskResult = (int)DTSExecResult.Failure;
}
}
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
}
}
I hope you found this tutorial helpful. In the next post, I will show you how to upload a file using SSIS and the WinSCP.net assembly.