Uploading Files to SFTP Server using SSIS

By default SSIS will not give you an option to upload in an SFTP server, the only option you will see in the SSIS toolbox is for FTP so if you are planning to automate your upload function in a secure manner it will not be straightforward.  Luckily SSIS have the option like the Script Task and Execute Process Task, combining this with WinSCP will easily sort your problems.

Like what I said it’s not straightforward but it’s easily achievable and it would look as simple as this one.

Let start!

First you need to have WinSCP installed on the machine that will run your package.  This component is the actual application that will upload your files, it will just be invoked by SSIS via a script that it will automatically generate.

I am assuming you already have automated file generators like the sequence container you see above where it generates file you needed to upload whether its done by SSRS or other means.

All you need to do next is to dynamically generate your script depending on the filenames of the file you wish to upload.  Now create some variables needed by your script like your File Details, Proxy Details and SSH/SFTP Details like such:

Now that you have your variables you can now place a script task just after your file creation process.  Indicate first the variables that you will be using.

then in the Main method automatically generate a script that will then be run by WinSCP on the next step.

public void Main()
{
    try
    {
        var scriptContent = new StringBuilder();
 
        scriptContent.AppendLine("# Automatically abort script on errors");
        scriptContent.AppendLine("option batch abort");
        scriptContent.AppendLine(string.Empty);
 
        scriptContent.AppendLine("# Disable overwrite confirmations that conflict with the previous");
        scriptContent.AppendLine("option confirm off");
        scriptContent.AppendLine(string.Empty);
 
        scriptContent.AppendLine("# Connect to SFTP server using a password");
        scriptContent.AppendLine(string.Format("open sftp://{0}:{1}@{2}/ -hostkey=\"ssh-rsa 2048 {3}\" -rawsettings ProxyMethod=3 ProxyHost={4} ProxyPort={5}", 
            Dts.Variables["User::SSHUserName"].Value, 
            Dts.Variables["User::SSHPassword"].Value, 
            Dts.Variables["User::SSHServer"].Value, 
            Dts.Variables["User::SSHRSAFingerprint"].Value, 
            Dts.Variables["User::ProxyServer"].Value, 
            Dts.Variables["User::ProxyPort"].Value));
        scriptContent.AppendLine(string.Empty);
 
        scriptContent.AppendLine("# Upload file");
        scriptContent.AppendLine(string.Format("put {0}", Dts.Variables["User::FilePathCSV"].Value.ToString()));
        scriptContent.AppendLine(string.Empty);
 
        scriptContent.AppendLine("# Disconnect");
        scriptContent.AppendLine("close");
 
 
        string path = string.Format("{0}\\{1}", Dts.Variables["User::WinSCPDirectory"].Value, Dts.Variables["User::UploadScriptFile"].Value);
        //Delete File first if it exists
        if (File.Exists(path))
        {
            File.Delete(path);
        }
 
        // Create a file to write to. 
        using (StreamWriter sw = File.CreateText(path))
        {
            sw.WriteLine(scriptContent.ToString());
        }
 
 
        Dts.TaskResult = (int)ScriptResults.Success;
    }
    catch (Exception)
    {
        Dts.TaskResult = (int)ScriptResults.Failure;
    }
}

So what this script does is create a script file that WinSCP will run during the process.  The output will be something like this.

#Automatically abort script on errors
option batch abort
 
#Disable overwrite confirmations that conflict with the previous
option confirm off
 
#Connect to SFTP server using a password
open sftp://youruser:thepassword@theserver.com/ -hostkey="ssh-rsa 2048 00:00:00:00:00:00:00:00:00:00:00:00:00:00:00:00" -rawsettings ProxyMethod=3 ProxyHost=yourproxyserver.com ProxyPort=9999
 
#Upload file
put C:\MyFilesToUpload\MyFile1.csv
 
#Disconnect
close

If you want a more detailed information on how to do scripting using WinSCP refer to this otherwise this would be enough to let you start.   You have your SFTP details like the server, user, password and host key.  You will also notice this script indicates that you use a proxy server and the file/s you want to upload.

Now all you need is to pass this as a parameter for WinSCP in that Send Files via SFTP Execute Process Task, just indicate the executable path to your WinSCP executable, the arguments which is your autogenerated scrip above and the WorkingDirectory.

So if you are using a command line it will look like this.

WinSCP -script=C:\MyFilesToUpload\MyScript.txt

You can also add a log if you wish and its just a parameter like such

WinSCP -script=C:\MyFilesToUpload\MyScript.txt -log=C:\DatamineExtracts\WinSCP\Datamine.log

You can also dynamically generate those properties using variables and indicate it under the expressions tab.