Connecting to SFTP via SSIS

Question

I'm trying to connect to a SFTP server via an SSIS package. The package executes WinSCP with the following connection string in a .txt file:

open sftp://username:fc$#[email protected]:22

However the package keeps failing without being able to connect. Is it something to do with the special characters in the password?

I am able to connect to a different SFTP if I replace the string so I know it must be something to do with the syntax above. I've tried putting double quotes around the string as follows without any success:

open "sftp://username:fc$#[email protected]:22"

Solution 1

I had to do this too, for one of my work projects recently. We used the WinSCP .NET assembly inside an SSIS Scripting Task, as this is what WinSCP also recommends as the way to achieve SFTP using WinSCP in SSIS.

See this guide - Using WinSCP .NET Assembly from SQL Server Integration Services (SSIS). It walks you through the install and setup and also contains working sample code (after you change the script to your needs of course!).

Sample code - after you reference the WinSCPnet.dll assembly - is below.

using System;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Tasks.ScriptTask;
using System.AddIn;
using WinSCP;

namespace ST_5a30686e70c04c5a8a93729fd90b8c79.csproj
{
    [AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    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::SshHostKeyFingerprint"].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;
                    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;
            }
        }
    }
}

Solution 2

Install WinSCP and then create a folder where you want a file from client or put the file.Then Open a Execute Process Task and then go to Expression tab and set the Executable and Arguments with below codes(Please change accordingly). Write this code in notepad and save as winscp.txt at the path C:\path\to\winscp.txt.

Open sftp://Host_Name:[email protected]/ -hostkey="ssh-rsa 2048 xxxxxxxxxxx...="

get -delete /home/client/Share/MediaData/Media_file.xlsx

exit

Comments

[vikrant rana]

@Martin Prikryl .. Can we use above method to get files from SFTP server as well? I have used below statement to get the files from WinSCP folder 'my_data' transferResult = session.GetFiles("/my_data/ABC_EMP*",@"G:\TestFolder\Downloa‌​d\"); It's giving me an error message like "Exception has been thrown by the target of an invocation" Kindly suggest

[Martin Prikryl]

@vikrantrana Yes you can. If you have a problem, post a new question and make sure you include the actual exception message (the inner exception of "Exception has been thrown by the target of an invocation") or a session log file (Session.SessionLogPath).

[Martin Prikryl]

See also WinSCP article Exception has been thrown by the target of an invocation or Stack Overflow question SSIS C# 2012 Script Task referring WinSCPnet.dll fails when run from SQL Server Agent with “Exception has been thrown by the target of an invocation”.