Using WinSCP .NET Assembly from SQL Server Integration Services (SSIS)

First, you need to install the WinSCP .NET assembly.

You also need to install the assembly to the GAC or subscribe AppDomain.AssemblyResolve event to allow loading the assembly.

You use WinSCP .NET assembly from SSIS as any other .NET assembly:

• In Microsoft Visual Studio, in your “Integration Services Project”, choose your “SSIS Package” (e.g. the default Package.dtsx);
• Drag Script Task from SSIS Toolbox onto Control flow view of the package;
• In the context menu of the task, choose Edit;
• On the Script Task Editor on the initial Script page, click Edit Script button;
• Another instance of Visual Studio opens to edit the script project;
• Use Project > Add Reference > Browse to add reference to winscpnet.dll;
• Place your C# or VB.NET code into ScriptMain.Main method (see the example below);
• Close Visual Studio of the script project. Close Script Task Editor with OK button.

If you used GAC when developing your SSIS package, WinSCP .NET assembly needs to be installed to GAC even on the target machine.

using System;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Tasks.ScriptTask;
using WinSCP;
 
namespace ST_5a30686e70c04c5a8a93729fd90b8c79
{
    [SSISScriptTaskEntryPoint]
    public partial class ScriptMain : VSTARTScriptObjectModelBase
    {
        public void Main()
        {
            // Setup session options
            SessionOptions sessionOptions = new SessionOptions
            {
                Protocol = Protocol.Sftp,
                // To setup these variables, go to SSIS > Variables.
                // To make them accessible from the script task, in the context menu of the
                // task, choose Edit. On the Script task editor on Script page,
                // select ReadOnlyVariables, and tick the below properties.
                HostName = (string) Dts.Variables["User::HostName"].Value,
                UserName = (string) Dts.Variables["User::UserName"].Value,
                Password = (string) Dts.Variables["User::Password"].Value,
                SshHostKeyFingerprint = (string) Dts.Variables["User::Fingerprint"].Value
            };
 
            try
            {
                using (Session session = new Session())
                {
                    // As WinSCP .NET assembly has to be stored in GAC to be used with SSIS,
                    // you need to set path to WinSCP.exe explicitly,
                    // if using non-default location.
                    session.ExecutablePath = @"C:\winscp\winscp.exe";
 
                    // Connect
                    session.Open(sessionOptions);
 
                    // Upload files
                    TransferOptions transferOptions = new TransferOptions();
                    transferOptions.TransferMode = TransferMode.Binary;
 
                    TransferOperationResult transferResult = session.PutFiles(
                        @"d:\toupload\*", "/home/user/", false, transferOptions);
 
                    // Throw on any error
                    transferResult.Check();
 
                    // Print results
                    bool fireAgain = false;
                    foreach (TransferEventArgs transfer in transferResult.Transfers)
                    {
                        Dts.Events.FireInformation(0, null, 
                            string.Format("Upload of {0} succeeded", transfer.FileName),
                            null, 0, ref fireAgain);
                    }
                }
 
                Dts.TaskResult = (int)DTSExecResult.Success;
            }
            catch (Exception e)
            {
                Dts.Events.FireError(0, null,
                    string.Format("Error when using WinSCP to upload files: {0}", e),
                    null, 0);
    
                Dts.TaskResult = (int)DTSExecResult.Failure;
            }
        }
    }
}

If you do not want to install the assembly to the GAC, you can instead subscribe AppDomain.AssemblyResolve event in a static constructor handler of the script task class to locate the assembly in an another location. For details, see article How to load an Assembly in a SSIS script task that isn’t in the GAC.

using System;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Tasks.ScriptTask;
using WinSCP;
 
namespace ST_5a30686e70c04c5a8a93729fd90b8c79
{
    [SSISScriptTaskEntryPoint]
    public partial class ScriptMain : VSTARTScriptObjectModelBase
    {
        private const string LoadingLog = @"C:\winscp\loading.log";
        private const string AssemblyPath = @"C:\winscp\WinSCPnet.dll";
 
        static ScriptMain()
        {
            DebugLoading("Setting up assembly resolve handler");
            AppDomain.CurrentDomain.AssemblyResolve += AssemblyResolve;
        }
 
        private static void DebugLoading(string message)
        {
            message = DateTime.Now.ToLongTimeString() + ": " + message + Environment.NewLine;
            // Uncomment to debug assembly loading issues
            // File.AppendAllText(LoadingLog, message);
        }
 
        private static Assembly AssemblyResolve(object sender, ResolveEventArgs args)
        {
            try
            {
                DebugLoading($"Resolving assembly {args.Name}");
                string name = new AssemblyName(args.Name).Name;
                DebugLoading($"Assembly name {name}");
                if (name.Equals("WinSCPnet", StringComparison.InvariantCultureIgnoreCase))
                {
                    DebugLoading($"Loading {name} from {AssemblyPath}");
                    Assembly assembly = Assembly.LoadFile(AssemblyPath);
                    DebugLoading("Loaded");
                    return assembly;
                }
                DebugLoading("Not WinSCPnet");
                return null;
            }
            catch (Exception e)
            {
                DebugLoading($"Exception: {e}");
                throw;
            }
        }
    }
}