Downloading Files from the SFTP Server
This post is part of a larger project. You will need to create the following project level variables. vDate, vSFTPInboundFileName, and vSftpLogFilename will use expressions to populate the variables.
Name Data Type Value
1.vDate String Current date in YYYYYMMDD format.
(DT_STR, 4, 1252) DATEPART("yyyy" , GETDATE())
+ RIGHT("0" + (DT_STR, 2, 1252) DATEPART("mm" , GETDATE()), 2)
+ RIGHT("0" + (DT_STR, 2, 1252) DATEPART("dd" , GETDATE()), 2)
2.vSftpServerUserName String User name for logging into SFTP server.
3.vSftpServerUserPassword String Password for logging into SFTP server.
4.vSftpInboundFileName String This value searches for all files beginning with "Test_", followed by the current date, ending with "-.zip" within the root directory."/Test_"+ @[User::vDate] +"_.zip"
5.vSftpInboundFilePath String This value is local. But can be anywhere on your network. C:\Staging
6.vSftpLogFilename String Path and name for the log file that will be generated during the WinSCP session. Date including the hour, minute, and second will be embedded in the file name. "C:\Logs\Document_Import_"+ @[User::vDate] +Right("0" + (DT_STR,2,1252) DATEPART("hh",GETDATE()),2) + Right("0" + (DT_STR,2,1252) DATEPART("mi",GETDATE()),2)
+ Right("0" + (DT_STR,4,1252) DATEPART("ss",GETDATE()),2)+"_log.txt"
7.vSftpServerUserName String The name or IP address of the server.
8.vSftpServerHostKey String The host key for the server.
You will need to obtain the WinSCP .exe and .dll files before implementing WinSCP functionality.
1. Drag the script task onto the SSIS canvas.
2.Configure the script task to use C# as the selected language.
3.Identify the ReadOnlyVariables and click on “OK”.
4.Your script task editor should resemble the following image.
5.Click on “Edit Script” and import the necessary namespaces.
#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using WinSCP;
using System.Collections.Generic;
using System.Net.Mail;
using System.IO;
using System.Text;
using System.Text.RegularExpressions;
#endregion
6.Enter the following code to call the WinSCP.net assembly. The path variable should reference your location for the assembly.
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:\lib_csharp\WinSCP.Net";
return System.Reflection.Assembly.LoadFile(System.IO.Path.Combine(path, "WinSCPnet.dll"));
}
return null;
}
//end
7. Create the script variables.
public void Main()
{
// Set up session options
SessionOptions sessionOptions = new SessionOptions
{
Protocol = Protocol.Sftp,
HostName = Dts.Variables["User::vSftpServerName"].Value.ToString(),
//PortNumber = 22, //Port is 22 by default.
UserName = Dts.Variables["User::vSftpUserName"].Value.ToString(),
Password = Dts.Variables["User::vSftpServerUserPassword"].Value.ToString(),
SshHostKeyFingerprint = Dts.Variables["User::vSftpServerHostKey"].Value.ToString(),
};
8. Encapsulate WinSCP code in try...catch statement.
Note the following script "Dts.Variables["User::vSftpInboundFilePath"].Value.ToString(), true, transferOptions);" The value of "true" will delete the file after the download is complete. Changing the value to "false" will leave the server file intact.
try
{
using (Session session = new Session())
{
session.Open(sessionOptions);
session.DebugLogLevel = 0;
//Create session log file.
session.DebugLogPath = Dts.Variables["User::vSftpLogFilename"].Value.ToString();
TransferOptions transferOptions = new TransferOptions();
transferOptions.TransferMode = TransferMode.Binary;
transferOptions.ResumeSupport.State = TransferResumeSupportState.Off;
//Download the files and delete the files on the server once download is complete.
TransferOperationResult vSftpTransferResult = session.GetFiles(Dts.Variables["User::vSftpInboundFileName"].Value.ToString(), Dts.Variables["User::vSftpInboundFilePath"].Value.ToString(), true, transferOptions);
// Throw on any error
vSftpTransferResult.Check();
// Print results
bool fireAgain = false;
foreach (TransferEventArgs transfer in vSftpTransferResult.Transfers)
{
Dts.Events.FireInformation(0, null,
string.Format("Download of {0} succeeded", transfer.FileName),
null, 0, ref fireAgain);
}
}
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;
}
The code in its entirety.
#region Help: Introduction to the script task
/* The Script Task allows you to perform virtually any operation that can be accomplished in
* a .Net application within the context of an Integration Services control flow.
*
* Expand the other regions which have "Help" prefixes for examples of specific ways to use
* Integration Services features within this script task. */
#endregion
#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using WinSCP;
using System.Collections.Generic;
using System.Net.Mail;
using System.IO;
using System.Text;
using System.Text.RegularExpressions;
#endregion
namespace ST_7196ea3db49a4422838421d1e71fd54a
{
/// <summary>
/// ScriptMain is the entry point class of the script. Do not change the name, attributes,
/// or parent of this class.
/// </summary>
[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
public object Lists { get; private set; }
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:\lib_csharp\WinSCP.Net";
return System.Reflection.Assembly.LoadFile(System.IO.Path.Combine(path, "WinSCPnet.dll"));
}
return null;
}
//end
public void Main()
{
// Set up session options
SessionOptions sessionOptions = new SessionOptions
{
Protocol = Protocol.Sftp,
HostName = Dts.Variables["User::vSftpServerName"].Value.ToString(),
//PortNumber = 22, //Update only if the port number needed for connection differs from port 22
UserName = Dts.Variables["User::vSftpUserName"].Value.ToString(),
Password = Dts.Variables["User::vSftpServerUserPassword"].Value.ToString(),
SshHostKeyFingerprint = Dts.Variables["User::vSftpServerHostKey"].Value.ToString(),
};
try
{
using (Session session = new Session())
{
session.Open(sessionOptions);
session.DebugLogLevel = 0;
session.DebugLogPath = Dts.Variables["User::vSftpLogFilename"].Value.ToString();
TransferOptions transferOptions = new TransferOptions();
transferOptions.TransferMode = TransferMode.Binary;
transferOptions.ResumeSupport.State = TransferResumeSupportState.Off;
TransferOperationResult vSftpTransferResult = session.GetFiles(Dts.Variables["User::vSftpInboundFileName"].Value.ToString(), Dts.Variables["User::vSftpInboundFilePath"].Value.ToString(), true, transferOptions);
// Throw on any error
vSftpTransferResult.Check();
// Print results
bool fireAgain = false;
foreach (TransferEventArgs transfer in vSftpTransferResult.Transfers)
{
Dts.Events.FireInformation(0, null,
string.Format("Download of {0} succeeded", transfer.FileName),
null, 0, ref fireAgain);
}
}
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;
}
}
#region ScriptResults declaration
/// <summary>
/// This enum provides a convenient shorthand within the scope of this class for setting the
/// result of the script.
///
/// This code was generated automatically.
/// </summary>
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion
}
}