https://winscp.net/eng/docs/library_ssis
I am trying to follow this script to eventually get all csv files from a directory on our local network uploaded to an SFTP server. I don't get any errors when I build/rebuild the script task, but when I run it, immediately fails with "Exception has been thrown by the target of an invocation." Nothing shows up in the log at all. I am trying to get at least one file to upload by itself to make sure the upload piece is working. Then I want to modify it to upload all csv files in the same directory. Any tips or pointers in how to troubleshoot this will be greatly appreciated.
[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["$Project::prmHost"].Value,
UserName = (string)Dts.Variables["$Project::prmUser"].Value,
Password = (string)Dts.Variables["$Project::prmPassword"].Value,
SshHostKeyFingerprint = (string)Dts.Variables["$Project::prmFingerprint"].Value,
PortNumber = Convert.ToInt32(Dts.Variables["$Project::prmPort"].Value)
};
try
{
using (Session session = new Session())
{
// If WinSCP .NET assembly has been stored in GAC to be used with SSIS,
// you need to set path to WinSCP.exe explicitly.
// This is not needed if you have subscribed AppDomain.AssemblyResolve event
// and the WinSCP.exe is in the same location as WinSCPnet.dll.
//session.ExecutablePath = @"C:\winscp\winscp.exe";
session.SessionLogPath = Dts.Variables["$Project::prmLogFileName"].Value.ToString();
// Connect
session.Open(sessionOptions);
string remotePath = Dts.Variables["$Project::prmPath"].Value.ToString();
string localPath = Dts.Variables["$Project::prmLocalPath"].Value.ToString() + "etrainingusers_0.csv";
// Upload files
TransferOptions transferOptions = new TransferOptions();
transferOptions.TransferMode = TransferMode.Binary;
TransferOperationResult transferResult = session.PutFiles(
localPath, remotePath, 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;
}
}
}
This is the currently working code to upload one single .csv file.
#region Namespaces
using System;
using System.Windows.Forms;
using System.IO;
using System.Reflection;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Tasks.ScriptTask;
using WinSCP;
#endregion
namespace ST_4977fa724bbb4816bfe3d3c8f2376a79
{
[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
private const string LoadingLog = @"C:\winscp\elearningupload.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;
}
}
public class AppException : Exception
{
public AppException(String message) : base(message)
{ }
public AppException(String message, Exception inner) : base(message, inner)
{ }
}
public void Main()
{
SessionOptions sessionOptions = new SessionOptions
{
Protocol = Protocol.Sftp,
HostName = (string)Dts.Variables["$Project::prmHost"].Value,
UserName = (string)Dts.Variables["$Project::prmUser"].Value,
Password = (string)Dts.Variables["$Project::prmPassword"].Value.ToString(),
SshHostKeyFingerprint = (string)Dts.Variables["$Project::prmFingerprint"].Value,
PortNumber = Convert.ToInt32(Dts.Variables["$Project::prmPort"].Value)
};
try
{
using (Session session = new Session())
{
session.ExecutablePath = @"C:\winscp\winscp.exe";
session.SessionLogPath = Dts.Variables["$Project::prmLogFilePath"].Value.ToString();
//session.SessionLogPath = @"C:\winscp\elearningupload.log";
//Directories
string fname = Dts.Variables["$Project::prmFilename"].Value.ToString();
string locpath = Dts.Variables["$Project::prmLocalPath"].Value.ToString(); //This is the local directory
string path = Dts.Variables["$Project::prmPath"].Value.ToString(); //This is the desired remote directory
string fullname = locpath + fname;
// Connect
session.Open(sessionOptions);
// Upload files
TransferOptions transferOptions = new TransferOptions();
transferOptions.TransferMode = TransferMode.Binary;
transferOptions.PreserveTimestamp = false;
var permissions = new FilePermissions { Octal = "0750" };
transferOptions.FilePermissions = permissions;
TransferOperationResult transferResult = session.PutFiles(@fullname, @path, 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)ScriptResults.Success;
}
catch (AppException e)
{
//Raise Error event
Dts.Events.FireError(0, "Script Task", "Error", string.Empty, 0);
if (e.InnerException != null)
Console.WriteLine("Inner exception: {0}", e.InnerException);
Dts.Events.FireError(0, null,
string.Format("Error when using WinSCP to upload files: {0}", e),
null, 0);
Dts.TaskResult = (int)DTSExecResult.Failure;
}
}
#region ScriptResults declaration
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion
}
}