Post a reply

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

Options

Topic review

Guest

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

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

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/*");
                    }
                }
 
                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;
            }
        }
    }
}
martin

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

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

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

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

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:

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

Thank you very much in advance.