2

I am trying to connect to winscp and using below code as a reference to get the zip files from SFTP server. I have added reference for the WinSCPnet.dll assembly. I want to download the ABC_EMP.txt.zip which is placed at Winscp folder location my_data.

Sample-code:

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

namespace ST_5a30686e70c04c5a8a93729fd90b8c79.csproj
{

    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,
                PortNumber = 22,
                SshHostKeyFingerprint = (string) Dts.Variables["User::SshHostKeyFingerprint"].Value
            };

            try
            {
                using (Session session = new Session())
                {
                     session.SessionLogPath="G:/Testing/SFTP_Log_Details/winscp.log";
                    // 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.GetFiles("/my_data/ABC_EMP.txt.zip",@"G:\TestFolder\Download\");

                    // 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;
            }
        }
    }
}

I have generated 'SshHostKeyFingerprint'as "ssh-rsa 2048 9b:63:5e:c4:26:bb:35:0d:49:e6:74:5e:5a:48:c0:8a"

enter image description here

The script task gets executed and failng with below error code "exception has been thrown by the target of an invocation". Log folder is also empty with no log written.

vikrant rana
  • 4,509
  • 6
  • 32
  • 72
  • 1
    Did you see [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”](https://stackoverflow.com/q/34345160/850848)? – Martin Prikryl Jun 24 '19 at 10:42
  • @@ Martin Prikryl.. I have read lot of your great post in last couple of days.. Those posts really helped me a lot to set up and configure my ssis packages.. Thanks ☺️ – vikrant rana Jun 24 '19 at 16:21
  • (the actual answers, not my comments here) – Martin Prikryl Jun 24 '19 at 16:41

0 Answers0