Post a reply

Add an Attachment

If you do not want to add an Attachment to your Post, please leave the Fields blank.

(maximum 10 MB; please compress large files; only common media, archive, text and programming file formats are allowed)


Topic review



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 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...

Re: Setting up WinSCP SSIS package for SQL server agent job

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;

namespace ST_331963c4d11c478fb003abcd00418809

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

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


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

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


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;


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

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

Re: Setting up WinSCP SSIS package for SQL server agent job

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?

Setting up WinSCP SSIS package for SQL server agent job


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

- 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:
- 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:
- 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.