Downloading SFTP Files in SSDT

I often need to download files from an SFTP site in SQL Server Data Tools (SSDT), the best way I’ve found of doing this is a script task that hooks into WinSCP.

WinSCP and it’s dlls can be downloaded here. Once installed, the dll should also be added to the GAC on the server that the package will be running on.

I then create the following variables in the package to store the required values and make the script dynamic.

• sftpSourceHostKey
• sftpSourceRemoteFolder
• sftpSourceFilePatten
• sftpSourceServerAddress
• sftpSourceUserName
• localWinScpExecutable
• localDownloadFolder

These are then brought through into the script task and used as so, the WinSCP dll also needs to be added as a reference in the script so it can be accessed.

public void Main()
{
	// Load in SSIS variables
	string password = Dts.Variables["User::sftpSourceEncryptedPassword"].Value.ToString();
	string hostKey = Dts.Variables["User::sftpSourceHostKey"].Value.ToString();
	string remoteFolder = Dts.Variables["User::sftpSourceRemoteFolder"].Value.ToString();
	string searchPattern = Dts.Variables["User::sftpSourceFilePatten"].Value.ToString();
	Regex regexPattern = new Regex(searchPattern);
	string serverAddress = Dts.Variables["User::sftpSourceServerAddress"].Value.ToString();
	string userName = Dts.Variables["User::sftpSourceUserName"].Value.ToString();
	string winScpExecutable = Dts.Variables["User::localWinScpExecutable"].Value.ToString();
	string downloadFolder = Dts.Variables["User::localDownloadFolder"].Value.ToString();
	
	if (!downloadFolder.EndsWith("\\"))
	{
		downloadFolder += "\\";
	}
	
	// Create WinSCP session
	Session session = new Session();
	session.DisableVersionCheck = true;
	session.ExecutablePath = winScpExecutable;
	
	// Create WinSCP session options
	SessionOptions sessionOptions = new SessionOptions();
	sessionOptions.HostName = serverAddress;
	sessionOptions.UserName = userName;
	sessionOptions.Password = password;
	sessionOptions.SshHostKeyFingerprint = hostKey;
	sessionOptions.GiveUpSecurityAndAcceptAnySshHostKey = true;
	
	// Create WinSCP transfer options
	TransferOptions transferOptions = new TransferOptions();
	transferOptions.ResumeSupport.State = TransferResumeSupportState.On;
	transferOptions.PreserveTimestamp = false;
	transferOptions.FilePermissions = null;
	transferOptions.TransferMode = TransferMode.Automatic;
	
	// Open Session
	session.Open(sessionOptions);
	
	// Get list of all objects in target directory
	RemoteDirectoryInfo remoteDirectory = session.ListDirectory(remoteFolder);
	
	// Itterate through the files
	foreach (RemoteFileInfo file in remoteDirectory.Files)
	{
		if (file.IsDirectory == false && regexPattern.IsMatch(file.Name) == true)
		{
			// Download each file and remove from SFTP site when done
			TransferOperationResult result = session.GetFiles(remoteFolder + file.Name, downloadFolder + file.Name, true, transferOptions);
			// Will throw an error if not successful result.Check();
		}
	}
	
	session.Close();

	Dts.TaskResult = (int)ScriptResults.Success;
}