Setting up WinSCP SSIS package for SQL server agent job

Greetings,

I was given the task to automatize file transfer and load from an SFTP source into our data warehouse (MS SQL). The SSIS package which I created runs like a charm in Visual Studio (on client machine) but fails miserably on the SQL server (deployed on SSISDB and run with SQL server agent job).

Details:
- I am using script task with .net code
- The WinSCP Assembly is placed in a folder on the Server (eg.: //server/xy/winscpnet.dll)
- Instead of registering to GAC I am using the workaround proposed here:
https://docs.microsoft.com/en-us/archive/blogs/dbrowne/how-to-load-an-assembly-in-a-ssis-script-task-that-isnt-in-the-gac
- SFTP server and user (and proxy) credentials are defined within the code
- As mentioned runs very smooth from the client machine of mine.
- When running from Job agent the Script task fails with the following error msg:
- https://winscp.net/eng/docs/message_net_exception_thrown_target_invocation
- Tried to log the run but the log is empty
- The SSISDB proxy has access to the folder (as well as the server technical user as we are getting other files on a daily basis)

If needed I can provide code snippet.
I would really appreciate if someone could help me woth this.

Thank you very much in advance.

So did you try to capture the inner exception as the article suggests?
https://winscp.net/eng/docs/message_net_exception_thrown_target_invocation

martin wrote:

So did you try to capture the inner exception as the article suggests?
https://winscp.net/eng/docs/message_net_exception_thrown_target_invocation

Yes I have and nothing seems to be working. :(

Yes I have and nothing seems to be working. :(

Sorry, but that's too vague. What did you try exactly and what did you get?

martin wrote:

Yes I have and nothing seems to be working. :(

Sorry, but that's too vague. What did you try exactly and what did you get?

I would just provide my code:


#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using WinSCP;
using System.Collections.Generic;
#endregion

namespace ST_331963c4d11c478fb003abcd00418809
{

[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
static ScriptMain() // code responsible for GAC

{
AppDomain.CurrentDomain.AssemblyResolve += new ResolveEventHandler(CurrentDomain_AssemblyResolve);
}
static System.Reflection.Assembly CurrentDomain_AssemblyResolve(object sender, ResolveEventArgs args)
{
if (args.Name.Contains("WinSCPnet"))
{
string path = @"\\server\WinSCP\";
return System.Reflection.Assembly.LoadFile(System.IO.Path.Combine(path, "WinSCPnet.dll"));
}

return null;




}
public void Main()
{
try
{

SessionOptions sessionOptions = new SessionOptions
{
Protocol = Protocol.Sftp,
HostName = "hostnem",
UserName = "username",
Password = "password",
SshHostKeyFingerprint = "fingerprint",
};

sessionOptions.AddRawSettings("ProxyMethod", "2");
sessionOptions.AddRawSettings("ProxyHost", "someproxy");
sessionOptions.AddRawSettings("ProxyPort", "someport");
sessionOptions.AddRawSettings("ProxyUsername", "someusername");
sessionOptions.AddRawSettings("ProxyPassword", "Somepw");


using (Session session = new Session())
{


session.Open(sessionOptions);

TransferOptions transferOptions = new TransferOptions();
transferOptions.TransferMode = TransferMode.Binary;

TransferOperationResult transferResult = session.GetFiles(
"/*.csv", @"\\server\folder\", false, transferOptions);

transferResult.Check();

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);

session.MoveFile(transfer.FileName, "/Archive/*");
}

//IEnumerable<RemoteFileInfo> currFiles = session.EnumerateRemoteFiles("/", null, EnumerationOptions.None);

//foreach (TransferEventArgs transfer in transferResult.Transfers)
//{
// session.MoveFile(transfer.FileName,"/Archive/");
//}

}



Dts.TaskResult = (int)DTSE xecResult.Success;
}
catch (Exception e)
{

Variables exceptionVariable = null;
Dts.VariableDispenser.LockOneForWrite("User::problemvar", ref exceptionVariable);

exceptionVariable["User::problemvar"].Value = e.Message;


exceptionVariable.Unlock();

Dts.Events.FireError(0, null,
string.Format("Error when using WinSCP to upload files: {0}", e),
null, 0);

Dts.TaskResult = (int)DTS ExecResult.Failure;
}
}
}
}

Update:

I managed to get the error msg:

An attempt was made to load an assembly from a network location which would have caused the assembly to be sandboxed in previous versions of the .NET Framework. This release of the .NET Framework does not enable CAS policy by default, so this load may be dangerous. If this load is not intended to sandbox the assembly, please enable the loadFromRemoteSources switch. See http://go.microsoft.com/fwlink/?LinkId=155569 for more information.RuntimePropertyInfo:System.Collections.ListDictionaryInternalRuntimePropertyInfo:RuntimePropertyInfo:System.Reflection.RuntimeAssembly nLoadFile(System.String, System.Security.Policy.Evidence)RuntimePropertyInfo: at System.Reflection.RuntimeAssembly.nLoadFile(String path, Evidence evidence)

I am attempting to solve this now...